Copying PostgreSQL Databases Schema and Data

developers toolsdatabaseTutorial
4 min read

When working with PostgreSQL, you’ll often need to copy databases. Whether it’s for backups, setting up development environments, or migrating between servers, understanding how to efficiently copy your PostgreSQL databases is crucial. This post focuses on copying PostgreSQL databases of the same version, covering schema-only copies, full copies (schema and data), and server-to-server transfers.

Copying Schema Only

If you need to replicate just the structure of your database without the data, pg_dump with the --schema-only option is your go-to tool. This is incredibly useful for:

  • Setting up development or testing databases.
  • Version controlling database schema changes.
  • Preparing for data migration by creating the target schema first.

Using pg_dump --schema-only

The basic command structure is:

pg_dump --schema-only -U <username> -d <database_name> -f <output_file.sql>

Let’s break down the key options:

  • --schema-only: This is the essential flag that tells pg_dump to only export the schema (table definitions, functions, indexes, etc.) and not the data itself.
  • -U <username>: Specifies the PostgreSQL username to connect with.
  • -d <database_name>: The name of the source database you want to copy.
  • -f <output_file.sql>: The name of the file where the schema SQL will be saved.

Enhancing Your Schema Export

Here are some useful options to add to your pg_dump command:

  • -W: Force pg_dump to prompt for a password.
  • -h <hostname>: Specify the hostname of the PostgreSQL server if it’s not local.
  • -p <port>: Specify the port number if PostgreSQL is running on a non-default port.
  • -C or --create: Include the CREATE DATABASE command in the output file. This is helpful if you want the dump file to create the database when you restore it.
  • -c or --clean: Include commands to DROP existing database objects before creating new ones. Useful for ensuring a clean schema import.
  • --if-exists: Use with --clean to add IF EXISTS clauses to the DROP commands, preventing errors if objects don’t exist in the target database.
  • --table=<pattern> and --exclude-table=<pattern>: For selectively including or excluding specific tables based on patterns. For example, --table=public.users* would include tables starting with “users” in the public schema.

Example Command (Single Database Schema Only):

pg_dump --schema-only -U db_admin -d source_db -f schema_only_dump.sql -h db.example.com -p 5432 -C -c --if-exists

Basic Schema Export

# Basic schema-only export
pg_dump --schema-only \
  -U <username> \
  -d <database_name> \
  -f <output_file.sql>

Advanced Schema Export Options

# Enhanced schema export with additional options
pg_dump --schema-only \
  -U db_admin \
  -d source_db \
  -f schema_only_dump.sql \
  -h db.example.com \
  -p 5432 \
  -C \
  -c \
  --if-exists

Copying All Databases Schema Only

To export the schema of all databases in your PostgreSQL cluster (excluding template databases like template0 and template1), you can use pg_dumpall with the -s (or --schema-only) option.

Using pg_dumpall --schema-only

pg_dumpall -s -U <username> -f all_schemas_dump.sql
  • -s or --schema-only: Exports only the schema of all databases.
  • -U <username>: Specifies the PostgreSQL superuser username (required for pg_dumpall).
  • -f <all_schemas_dump.sql>: The output file for all schemas.

Example Command (All Databases Schema Only):

pg_dumpall -s -U postgres -f all_schemas_dump.sql -h localhost -p 5432 -C

Copying All Database Schemas

# Export all database schemas
pg_dumpall -s \
  -U postgres \
  -f all_schemas_dump.sql \
  -h localhost \
  -p 5432 \
  -C

Directly Copying All Database Schemas to a Target Server

You can combine pg_dumpall and psql with a pipe to directly copy all database schemas to a target server without saving to a local file first. This is efficient for server-to-server schema replication.

Example Command (Direct Schema Copy to Target):

export PGPASSWORD=your_source_password  # Set password for source user (if needed)
pg_dumpall -C -h <source_host> -p <source_port> -U <source_user> -s | PGPASSWORD=your_target_password psql -h <target_host> -U <target_user> -d postgres -p <target_port>

⚠️ Security Warning: The examples in this post use plain text passwords for demonstration purposes. In production environments, you should use .pgpass file or connection service files for secure password management.

Let’s break down this command:

  • export PGPASSWORD=your_source_password: Sets the password for the source PostgreSQL user as an environment variable. Important: While convenient, be cautious about exposing passwords in your shell history. Consider more secure methods for production environments.
  • pg_dumpall -C -h <source_host> -p <source_port> -U <source_user> -s: This is the pg_dumpall command:
    • -C: Includes CREATE DATABASE statements in the output.
    • -h <source_host>: Hostname of the source server. Use localhost if it’s on the same machine.
    • -p <source_port>: Port of the source server (default is 5432).
    • -U <source_user>: PostgreSQL superuser on the source server (e.g., postgres).
    • -s: Specifies schema-only dump.
  • |: The pipe symbol directs the output of pg_dumpall to the input of psql.
  • PGPASSWORD=your_target_password psql -h <target_host> -U <target_user> -d postgres -p <target_port>: This is the psql command:
    • PGPASSWORD=your_target_password: Sets the password for the target PostgreSQL user.
    • psql: The PostgreSQL command-line client.
    • -h <target_host>: Hostname of the target server. Use localhost if it’s the same machine but a different port.
    • -U <target_user>: PostgreSQL user on the target server (e.g., postgres).
    • -d postgres: Connect to the postgres database on the target server. You can use any existing database as pg_dumpall output includes CREATE DATABASE commands.
    • -p <target_port>: Port of the target server (e.g., 5433 in your example).

Important Notes for Direct Pipe Copy:

  • Password Security: Using PGPASSWORD in environment variables is shown for example purposes. For production, explore more secure password management methods.
  • User Permissions: Ensure the source user has sufficient privileges to dump all databases and the target user has privileges to create databases and schemas on the target server.
  • Target Database: In the psql part of the command, we connect to the postgres database on the target server. This is just a starting point; the pg_dumpall output will contain CREATE DATABASE statements to create all the databases on the target server based on the dumped schemas.

Restoring the Schema

To import the schema into a new database from a file (e.g., all_schemas_dump.sql), use psql:

psql -U <username> -d <target_database_name> -f <all_schemas_dump.sql>

If your pg_dumpall command included -C, the dump file will attempt to create databases if they don’t exist.

  • Home
  • About