Postgresql Import dump with user dfferent from postgres

Question

i'm tring to backup my database from production machine to a standby machine.

My production machine is a debian (unspecified version) distro

My standby machine is a ubuntu 20.10 distro

After some readings across the internet here is what i was able to accomplish

pg_dump -h 123.123.123.123 -p 5432 my_db > my_db.bak -> check
psql < createDateabase.sql -> check this make a simple drop/create database my_db owner my_db_backup;
psql < my_db.back -> check

so far so good

connect with my sqlworkbench to db -> check
use table imported -> ehm, not check

the problem is the ownership of the tables and sequeces. When imported, postgres is their owner but i want my_db_backup as owner insted.

I have tryed, as a workaround, to

alter table xxx OWNER TO my_db_backup; - > check for each table
alter sequence if exists xxx OWNER TO my_db_backup; -> it takes forever and it is unable to finish;

the command for the final stage (change ownership) is psql < finalize.sql

and finalize.sql is

\connect my_db;
ALTER TABLE xxx OWNER TO my_db_backup;
ALTER SEQUENCE IF EXISTS seq_xxx OWNER TO my_db_backup; -- this takes forever

what i'm missing?

I would prefer to import directly with the right owner instead of altering owner after, but that's still acceptable as long as i can change all objects in my_db

tnx for the help

I don't understand why you would want to change ownership of all the objects for the standby. Why not keep it owned by the same owner? — Jeremy
– Jeremy, Commented Apr 18, 2021 at 2:02
@Jeremy well stand by is not totally standby :) the orginal user is busy with something else that don't need to see this database — Alex75
– Alex75, Commented Apr 22, 2021 at 12:02

Alex75 · Accepted Answer · 2021-04-17 17:30:03Z

I was finally able to accomplish the import with a different user as follow

psql -U my_db_backup < my_db.bak

to avoid asking for password i had to make an entry in pg_hba.conf like this

local my_db    my_db_backup  trust

and to avoid request for password during the pg_dump i had to add this entry in my production machine

host  my_db  postgres     456.456.456.456/32    trust

where 456.456.456.456 id the ip of the standby machine.

i also had to change a bit the pg_dump to avoid errors for different user import

pg_dump -h 123.123.123.123 -p 5432 --no-owner --no-acl my_db > my_db.bak

so the complete script looks like

pg_dump -h 123.123.123.123 -p 5432 --no-owner --no-acl my_db > my_db.bak
psql < createDatabase.sql
psql -U my_db_backup < my_db.bak

EDIT: to make the script usable from any user there are a few things in need to be done

pg_dump -h 123.123.123.123 -p 5432 -U postgres --no-owner --no-acl -w my_db > my_db.bak
psql -U postgres < createDatabase.sql
psql -U my_db_backup < my_db.bak

Adding -U postgres is needed only because pg_dump try to login with the current user so if you are running the script as, for example, root pg_dump and psql will try to login as root and will obviously fail.

The other thing is the -w option that is needed to make pg_dump not ask for password.

Collectives™ on Stack Overflow

Postgresql Import dump with user dfferent from postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related