Newby question, help needed to make copy of DB to local

[AntonyNaude]

I am fairly new to dbeaver and I can basically run SQL statements to manage data, etc.
I am helping a local owl sanctuary volunteer org with their database and I need to cleanup some incorrectly captured data.
I want to copy a complete PostgreSQL database with tables and all, to local, so that I can test my SQL queries, etc. without causing an issues to the existing online database.
Please help.

[sknextcode]

In dbeaver, just right click on the database and select Tools > Dump database. This will open the wizard to dump your database to a local .backup file. You can then use this file on your local database with right click on database again > Tools > restore database.
If you have selected all items in the dump wizard, dbeaver dumps all of the table structure, table data, views, functions, sequences, etc.. Only thing that will be missing are postgreSQL extensions. But you can look them up under extensions in the productive database and install them on your local database using CREATE EXTENSION (https://www.postgresql.org/docs/9.1/sql-createextension.html) if needed.

Be aware that we currently experiencing some problems when restoring with latest dbeaver version ( 21.0.3.202104181339 ) and windows 10. So you might have to use the command line, an older Version of dbeaver or another tool to restore the database on your local setup for now.

[AntonyNaude]

Backup, got it. But now I'm failing at creating a local DB, pls see screen dumps above

[sknextcode]

Okay, so first of all you need to setup a local DB server (e.g. take a look at https://www.postgresqltutorial.com/install-postgresql/ on how to setup postgresql locally).
After that add a new connection to your local server in dbeaver, create a database and restore the backup into that database.

[sknextcode]

The official Download page is: https://www.postgresql.org/download/ but will redirect you to EDB anyway. So both ways will work the same.

[AntonyNaude]

thank you very much for your assistance, I really appreciate it.

[AntonyNaude]

mmm. looks like it only imported the database structure, no data.

[sknextcode]

Hi Antony,I'm currently not at my computer, but want to inform you, that as mentioned in my first reply, restoring with current version of dbeaver is not working. Please try an older version (7.3.5).We have reported this in the issues section aswell.As soon as I return home, I will try explaining another way to restore your dump file.

[AntonyNaude]

I am going to try this in the morning, it is rather late here, will report back on progress

[AntonyNaude]

Hi Sebastian, I used pgAdmin and it seems it "worked" in restoring the data however on the source DB some of the tables have foreign keys, etc. which did not pull through. I am going to try again.
ps the backup file has a sql extension.

[sknextcode]

Hi Antony, first of all, file extension .sql should be fine, as I just like to set it to .backup to tell apart, what ist a script and what is a backup. Do you get any other errors than the one I mentioned in your pgAdmin log after restoring?

[AntonyNaude]

yes, there was a few, but somehow it seems "ok"

[AntonyNaude]

the error reads "pg_restore: error: could not execute query: ERROR: role "data-user" does not exist"

[sknextcode]

Okay. So the role "data-user" exists on the remote database, but not your local one. You can try to add the parameters --no-privileges and --no-owner to pg_restore in order to avoid this. In pgAdmin this should be available in the options tab of the restore wizard. Just slide Owner and Privilige in "Do not save" to "Yes".

[sknextcode]

You can use both tools. It should not make a difference.
Since the backup contains the whole database, I suggest deleting the test database an creating a new one to rerun the restore procedure.
If you are still having trouble with the foreign keys: Take a look at the log if it shows any more errors, especially regarding foreign keys.
Another question is: Are those foreign key structures necessary for your work on cleaning up the data? (e.g. is data you need to work on relying on them?) If so, and if the problems with the foreign keys persist, you could try adding them manually after restore. This may save time over troubleshooting these issues.

[sknextcode]

ps, setting Owner and Privilige in "Do not save" to "Yes" worked perfectly, now I only have the expected error, "pg_restore: error: could not execute query: ERROR: schema "public" already exists"

Great news!

[AntonyNaude]

the foreign keys issues is solved

[sknextcode]

Very glad to hear that. So you can finally start working on the data, I guess.

Briefly summarized:

1. you've used DBeaver to make a backup of the remote database
2. installed Postgres to run a local database server
3. created a local database using DBeaver/pgAdmin
4. Restored the backup into your local database using pgAdmin (since DBeaver currently has an issue wiht that, see Reopening of #6103: dbeaver restore of pg_dump with custom format fails with "could not find block-id" #12293 for more information) and disabled owner and privileges upon restore (you can do so during backup aswell, but personally I would recommend choosing this on restore if needed)

[AntonyNaude]

Yes, big thanks for your help, I actually learned a lot.
I have deleted the test db a few times, recreated it, and imported the remote data. it works perfectly.

[sknextcode]

You're welcome. Glad, that I could help you getting started.

[venpisey12]

0100-05915377-12

[venpisey12]

0100-05915377-12