Search code examples
postgresqlpg-dumpdbeaver

Postgressql: copy data from prod database to dev database for some tables only

I've been tasked to copy some tables from the prod environment to the dev environment. These tables obviously exclude any user/account related tables. The databases are in separate RDS instances. The databases are datawarehouse_production and datawarehouse_development.

The issue I'm current facing is that I'm using DBeaver and am able to create DB dumps so for example, I create a dump for table 'responses' now when I try to use that dump and restore it to the dev environment, I get an error that says:

pg_restore: error: could not execute query: ERROR:  must be owner of relation responses

Command was: ALTER TABLE public.responses OWNER TO datawarehouse_production;

I can't even open the bloody dump files because they're binary. Here's what the dump file looks like: enter image description here

I wish to get rid of those queries that do for example: Drop database datawarehouse_production as the database I want to copy data to is datawarehouse_development.

I'm aware DBeaver has a transfer data feature but that is incredibly slow (50 mins for 20,000 rows). I've used it for smaller tables however the data I need to copy are 11 million rows.

I've also tried exporting the data to CSV from prod tables then loading them into dev tables however it's just as slow.

EDIT: After adding the --data-only flag: enter image description here

EDIT 2: Finally managed to make things work. Here's the backup settings window: enter image description here

And when restoring the dump, had to add --data-only flag in the extra command flags field!

Solution

  • Your database dump contains a lot of information required to replicate the exact state of the dumped tables, which includes permission-setting queries like the one that's exploding. If you already have the schema locally and don't want to replicate those sorts of things, try pg_restore --data-only to just load the data into your existing schema.