Exporting and importing using pg_dump and pg_restore

This page describes exporting and importing data into Cloud SQL instances using pg_dump and pg_restore.

Before you begin

Exports use database resources, but they do not interfere with normal database operations unless the instance is under-provisioned.

For best practices, see Best Practices for Importing and Exporting Data.

After completing an import operation, verify the results.

Exporting

Export data from an on-premises PostgreSQL server using pg_dump

To export a database that is not managed by Cloud SQL, for later import into Cloud SQL, use the pg_dump utility with the following flags:

  • --no-owner

    Ownership change commands must not be included in the SQL dump file.

  • --format

    Only plain SQL format is supported by the Cloud SQL API.

    The custom format is allowed if the dump file is intended for use with pg_restore.

  • --no-acl

    This flag is required if your dump would otherwise contain statements to grant or revoke membership in a SUPERUSER role.

In addition, you must remove all of the following:

  • Extension-related statements, if Cloud SQL does not support that extension. See PostgreSQL Extensions for the list of supported extensions.
  • CREATE EXTENSION or DROP EXTENSION statements referencing plpgsql. This extension comes pre-installed on Cloud SQL Postgres instances.
  • COMMENT ON EXTENSION statements.

For plain-text format: From a command line, run pg_dump:

pg_dump \
-U USERNAME \
--format=plain \
--no-owner \
--no-acl \
DATABASE_NAME | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' > SQL_FILE.sql

For custom format: From a command line, run pg_dump:

pg_dump \
-U USERNAME \
--format=custom \
--no-owner \
--no-acl \
DATABASE_NAME > DATABASE_NAME.dmp

The sed post-processing comments out all extension statements in the SQL dump file.

When importing using pg_restore, specify the processed table of content with the command-line argument "--use-list=DATABASE_NAME.toc".

Confirm that the default encoding, as determined by the database settings, is correct for your data. If needed, you can override the default with the --encoding flag.

To export in parallel, use the -j NUM_CORES flag. NUM_CORES is the number of cores on the source instance. Use the same flag with pg_restore to import in parallel.

For help with pg_dump, see the pg_dump reference.

Importing

Import data using pg_restore

You can use the pg_restore utility to import a database into Cloud SQL. pg_restore only works with archive files created by pg_dump. Learn more about pg_restore.

If the dump file was created with plain-text format, run the following command:

pg_restore \
-h CLOUD_SQL_INSTANCE_IP \
-U USERNAME \
--no-owner \
--no-acl \
-d DATABASE_NAME \
SQL_FILE.sql

If the dump file was created with custom format, run the following command:

pg_restore \
--list DATABASE_NAME.dmp | sed -E 's/(.* EXTENSION )/; \1/g' >  DATABASE_NAME.toc

The sed post-processing comments out all extension statements in the SQL dump file.

To import in parallel, use the -j NUM_CORES flag. NUM_CORES is the number of cores on the source instance. Use the same flag with pg_dump to export in parallel.

What's next