Postgresql How to Create a Copy of a Database or Schema

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

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;

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

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;

Creating a copy of a database in PostgreSQL

Postgres allows the use of any existing database on the server as a template when creating a new database. I'm not sure whether pgAdmin gives you the option on the create database dialog but you should be able to execute the following in a query window if it doesn't:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Still, you may get:

ERROR:  source database "originaldb" is being accessed by other users

To disconnect all other users from the database, you can use this query:

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();

How to clone a postgreSQL database with partial data

The best way I have found to do this is to first export the complete schema using the pg_dump tool with the -s flag (to dump schema only, and not data), and then export data separately.

To load your schemas starting from a fresh, empty database, use pg_restore. It will read the output from pg_dump and use it to build a database.

When exporting the data, you'll need to classify each table (you can write a helper script to make this easier, or use excel, etc...):

1. Tables that you want a subset of data from, based on some condition (ie. a certain value in person_id)
2. Tables you want to copy in their entirety (like dimension tables, such as calendar and company_locations)
3. Tables that you don't need any data from

For (1), you will need to write an appropriate SELECT query that returns the subset of data that you want to copy. Put those queries in a script and have each one write the result to a separate file, named <schema>.<table>. Lastly, use the psql utility to load the data to the test database. psql has a special \copy command that makes this easy. It can be used from a terminal like this:

psql --c "\copy schema.table FROM ‘~/dump_data/schema.table’ WITH DELIMITER ‘,’ CSV;"

Use pg_dump again to take care of all of those falling under (2), using the -t <table name> flag to dump only the named tables, and -a to dump only data (no schema). This could also be added to the script for (1) by just adding an unqualified SELECT * for each table in (2) and loading the data the same way.

Tables falling under (3) were already handled by the initial export.

How can I copy sequences from one schema to another in Postgresql?

After looking at the stack trace, I found references to schema 1 being made. For example, C:\...\CDbCommandBuilder.php(62): CDbConnection->getLastInsertID("public.lime_user_groups_ugid_seq"), where public is schema 1. And because schema 1 didn't appear in the pg_dump, I knew it must've been an issue with the app configs. I found there were a few hidden schema 1 strings to change in the codebase with the help of https://forums.limesurvey.org/forum/installation-a-update-issues/111790-installation-on-a-different-schema. This means the method I used to migrate/copy tables and their sequences from one schema to another works.

Update: The problem was in the app configs. Our database uses a pgpool connection with two pg servers. Connecting to the pool created the error. It went away after connected directly to the main/master pg server.

How to duplicate schemas in PostgreSQL

You can probably do it from the command line without using files:

pg_dump -U user --schema='fromschema' database | sed 's/fromschmea/toschema/g' | psql -U user -d database

Note that this searches and replaces all occurrences of the string that is your schema name, so it may affect your data.

---

Related Topics