CODEWITHSUNDEEP
sqldatabasepostgresql
Jin Kim
Jin Kim

Reputation: 17732

PostgreSQL how to create a copy of a database or schema?

Is there a simple way to create a copy of a database or schema in PostgreSQL 8.1?

I'm testing some software which does a lot of updates to a particular schema within a database, and I'd like to make a copy of it so I can run some comparisons against the original.

Upvotes: 46

Views: 74004

Answers (4)

Matthew Layne
Matthew Layne

Reputation: 131

This can be done by running the following command:

CREATE DATABASE [Database to create] WITH TEMPLATE [Database to copy] OWNER [Your username];

Once filled in with your database names and your username, this will create a copy of the specified database. This will work as long as there are no other active connections to the database you wish to copy. If there are other active connections you can temporarily terminate the connections by using this command first:

SELECT pg_terminate_backend(pg_stat_activity.pid) 
FROM pg_stat_activity 
WHERE pg_stat_activity.datname = '[Database to copy]' 
AND pid <> pg_backend_pid();

A good article that I wrote for Chartio's Data School which goes a bit more in depth on how to do this can be found here: https://dataschool.com/learn/how-to-create-a-copy-of-a-database-in-postgresql-using-psql

Upvotes: 11

Jordan S. Jones
Jordan S. Jones

Reputation: 13883

If it's on the same server, you just use the CREATE DATABASE command with the TEMPLATE parameter. For example:

CREATE DATABASE newdb WITH TEMPLATE olddb;

Upvotes: 47

Ali Raza Bhayani
Ali Raza Bhayani

Reputation: 3145

If you have to copy the schema from the local database to a remote database, you may use one of the following two options.

Option A

  1. Copy the schema from the local database to a dump file.

    pg_dump -U postgres -Cs database > dump_file

  2. Copy the dump file from the local server to the remote server.

    scp localuser@localhost:dump_file remoteuser@remotehost:dump_file

  3. Connect to the remote server.

    ssh remoteuser@remotehost

  4. Copy the schema from the dump file to the remote database.

    psql -U postgres database < dump_file

Option B

Copy the schema directly from the local database to the remote database without using an intermediate file.

pg_dump -h localhost -U postgres -Cs database | psql -h remotehost -U postgres database

This blog post might prove helpful for you if you want to learn more about options for copying the database using pg_dump.

Upvotes: 31

Matt K
Matt K

Reputation: 13852

pg_dump with the --schema-only option.

Upvotes: 37

Related Questions