Clonning Postgres DB from one remote server to another one

psqlcommands.md

Useful Postgres commands

• Terminate all connections to a database

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='';

psqlcopy.md

Copy Postgres DB from remote cluster

• Create the database on the destination server

create datatabase owner `owner_name`;

• If the source database has a role that the destination database does not have create the role.

create role `role_name`;

• If you want the database to have a different owner assign the created role to the databse

grant `created_role` to `db_owner`

• Create a backup of of the remote database by running

pg_dump -O -h `db_host` -U `db_user` `db_name` | gzip -9 > /path/outfile.sql.gz

• Restore the database on your destination server by running

zcat /path/outfile.sql.gz | psql -h `db_host` -U `db_user` `db_name`

• Drop the role that was created

drop role `role_name`

• When trying to detele the role that you needed to create you might get an error saying that there are objects that dependon that role and that is because the owner of the tables inside the created database is still the role that you want to delete. so to solve that just run this:

Note: You could pip it directly from one source to another after creating that database and role by doing:

pg_dump psql -h `db_host` -U `db_user` `db_name` | psql -h `db_host` -U `db_user` `db_name`