postgreSQL clone db - ghdrako/doc_snipets GitHub Wiki

PostgreSQL copy database within the same server

/* DISABLE NEW CONNECTIONS */
ALTER DATABASE <SOURCE_DB> WITH ALLOW_CONNECTIONS false;

/* KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)*/
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'SOURCE_DB' AND pid <> pg_backend_pid();

/* CLONE DATABASE TO NEW ONE(TARGET_DB) */
/* CREATE DATABASE [Database to create] WITH TEMPLATE [Database to copy] OWNER [Your username]; */
CREATE DATABASE TARGET_DB WITH TEMPLATE SOURCE_DB OWNER USER_DB;

/* ENABLE NEW CONNECTIONS */
ALTER DATABASE <SOURCE_DB> WITH ALLOW_CONNECTIONS true;

PostgreSQL copy database from a server to another:

  1. Create a Dump file of the source database.
pg_dump -U postgres -d source_database -f source_database.sql  # in local serwer

pg_dump -h [host address] -Fc -o -U [database user] <database name> > [dump file] # dump from remote server
  1. Copy the dump file to the remote server.
  2. Create a new database in the remote server where you want to restore the database dump:
CREATE DATABASE target_database;
  1. Restore the dump file on the remote server:
psql -U postgres -d target_database -f source_database.sql

For high-speed connections between servers or for smaller databases, you can also use the following command:

pg_dump -C -h local -U localuser source_database | psql -h remote -U remot

For instance, if one desires to copy the dvdrental database from the localhost to the remote server, you do it as follows:

pg_dump -C -h localhost -U postgres dvdrental | psql -h remote -U postgres dvdrental
⚠️ **GitHub.com Fallback** ⚠️