This page describes exporting and importing data into Cloud SQL instances usingpg_dump, pg_dumpall, and pg_restore.

Before you begin

For best practices, seeBest Practices for Importing and Exporting Data.

After completing an import operation,verify theresults.

Learn more about thepg_dump,pg_dumpall, andpg_restoreutilities.

Export data from Cloud SQL for PostgreSQL

You can use Cloud SQL to perform an export from the Google Cloud console,thegcloud CLI, or the API.

• To export a single PostgreSQL database, use thepg_dump utility.
• To export all PostgreSQL databases of a cluster, use thepg_dumpall utility.

When using either utility, make sure that you also use the required options toensure that the resulting export file is valid for import back into Cloud SQL.

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 thepg_dump utility with the following flags:

• --no-owner

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

• --format

  Thecustom anddirectory formats are allowed if the dump file is intended for use withpg_restore.

  Forplain-text format, export to aSQL dump file instead. This format is not compatible withpg_restore, and must be imported using the Google Cloud console import command orpsql client.

• --no-acl

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

• --clean

  This optional flag enables you to include theDROP <object> SQL statement that's required to drop (clean) database objects before you import them.

• --if-exists

  This optional flag enables you to include theIF EXISTS SQL statement with eachDROP statement that's produced by theclean flag.

In addition, you must remove all of the following:

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

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.

Export data using thecustom format from Cloud SQL for PostgreSQL

To use the custom format, from a command line, runpg_dump:

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

Export data from multiple files in parallel from Cloud SQL for PostgreSQL

You can only use thedirectory output format toexport data from multiple files in parallel.

To export in parallel, use the-jNUM_CORES flag.NUM_CORES is the number of cores on the source instance.

Export all databases

pg_dumpall isa utility that allows you to extract all PostgreSQL databases of a cluster intoa single script file. This file has SQL commands that you can use to restore thedatabases.

To export all PostgreSQL databases in a Cloud SQL instance, use thepg_dumpall utility with the following mandatory flags:

• exclude-database=cloudsqladmin
• exclude-database=template*

Thepg_dumpall utility doesn't have access to thecloudsqladmin ortemplate databases.

pg_dumpall\-hHOST_NAME-lDATABASE_NAME--exclude-database=cloudsqladmin\--exclude-database=template*>pg_dumpall.sql

To view role passwords when dumping roles withpg_dumpall,set thecloudsql.pg_authid_select_role flag to a PostgreSQL role name. If the role exists,then it has read-only (SELECT) access to thepg_authid table. This tablecontains role passwords.

Import

Use thepg_restore utility to import an archive into aCloud SQL database.pg_restore works only with archivescreated bypg_dump in either thecustom ordirectory formats.Learn more aboutpg_restore.

Import from a dump file created with thecustom format to Cloud SQL for PostgreSQL

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

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

Post-processing fromsed comments out all extension statements in the SQL dump file.

When importing usingpg_restore, specify the processed table of contents withthe command-line argument "--use-list=DATABASE_NAME.toc".

Import data from multiple files in parallel to Cloud SQL for PostgreSQL

You canimport data from multiple files in parallel only for archives created using thedirectory andcustom output formats.

To import in parallel, use the-jNUM_CORES flag.NUM_CORES is the number of cores on the destination instance.

Import performance on Cloud SQL for PostgreSQL

What's next

• Learn how tocheck the status of import and export operations.
• Learn more aboutbest practices for importing and exporting data.
• Learn more about thePostgreSQL pg_dump utility.
• Known issues for imports and exports.