PostgreSQL: make a copy of an existing database (includes tables and all records)

If the source database is owned by another user, you must login as super user, then run the command in psql:
CREATE new_db WITH TEMPLATE old_db OWNER src_db_owner
or run the command in terminal:
createdb -O src_db_owner -T src_db new_db

You may get errors if the source database is in use:
ERROR:  source database "src_db" is being accessed by other users
You can kill all the connections to the source databse:
SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'src_db' AND procpid <> pg_backend_pid();
then run the command
CREATE new_db WITH TEMPLATE src_db OWNER src_db_owner

No comments:

Post a Comment