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

Question

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.

here is a link to some [examples](http://www.cyberciti.biz/tips/howto-backup-postgresql-databases.html) of backing up and restoring. You can use the backup to restore to a different server or whatever — northpole, Apr 30 '09 at 19:59

score 43 · Accepted Answer · edited Apr 05 '19 at 00:25

43

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;

edited Apr 05 '19 at 00:25

Quaternion

10,380
6
51
102

answered Apr 30 '09 at 20:02

Jordan S. Jones

13,703
5
44
49

24

this also copied data to the new database... any related method to do it without data??? – Kush Nov 14 '11 at 08:16
2

Or `$ createdb -T olddb newdb` – Daniel Reis Sep 01 '14 at 15:19
Maybe yout get the following message error "source database “olddb” is being accessed by other users". To solve it, see this answer:https://stackoverflow.com/a/14375832/2123917. – mathias.horn Oct 18 '19 at 12:52
Use `OWNER` to set the new DB owner: `CREATE DATABASE newdb WITH TEMPLATE olddb OWNER new_db_user;` – Chalist Dec 27 '19 at 23:10

Matt K · Answer 2 · 2013-03-05T11:03:01.080

34

pg_dump with the --schema-only option.

edited Mar 05 '13 at 11:03

answered Apr 30 '09 at 19:57

Matt K

13,370
2
32
51

Nice, and it works also if the database is on a different server and under a high load. Thanks – Marco Carlo Moriggi Dec 14 '21 at 12:29

score 30 · Answer 3 · edited Apr 28 '16 at 11:24

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

Copy the schema from the local database to a dump file.
```
pg_dump -U postgres -Cs database > dump_file
```

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

scp localuser@localhost:dump_file remoteuser@remotehost:dump_file

Connect to the remote server.
```
ssh remoteuser@remotehost
```
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.

#3 says `db_name` doesnt exist. Is there a way to restore from schema without creating the db in prior. — , Apr 01 '16 at 05:46

Matthew Layne · Answer 4 · 2019-05-31T19:15:12.363

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

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

4 Answers4

Linked

Related