[[["易于理解","easyToUnderstand","thumb-up"],["解决了我的问题","solvedMyProblem","thumb-up"],["其他","otherUp","thumb-up"]],[["很难理解","hardToUnderstand","thumb-down"],["信息或示例代码不正确","incorrectInformationOrSampleCode","thumb-down"],["没有我需要的信息/示例","missingTheInformationSamplesINeed","thumb-down"],["翻译问题","translationIssue","thumb-down"],["其他","otherDown","thumb-down"]],["最后更新时间 (UTC):2025-08-07。"],[],[],null,["# Export and import using pg_dump, pg_dumpall, and pg_restore\n\n\u003cbr /\u003e\n\nMySQL \\| PostgreSQL \\| SQL Server\n\n\u003cbr /\u003e\n\n\u003cbr /\u003e\n\nThis page describes exporting and importing data into Cloud SQL instances using\npg_dump, pg_dumpall, and pg_restore.\n| **Note:** If you're migrating an entire database from a supported database server (on-premises, in AWS, or Cloud SQL) to a new Cloud SQL instance, you can use the [Database Migration Service](/database-migration/docs) instead of exporting and then importing files. If you're exporting because you want to create a new instance from the exported file, consider [restoring from a backup to a different instance](/sql/docs/postgres/backup-recovery/restoring#restorebackups-another-instance) or [cloning the instance](/sql/docs/postgres/clone-instance).\n\nBefore you begin\n----------------\n\n| **Important:** Before starting a large export, ensure that at least 25 percent of the database size is free (on the instance). Doing so helps prevent issues with aggressive autogrowth, which can affect the availability of the instance.\n\nExports use database resources, but they do not interfere with normal database\noperations unless the instance is under-provisioned.\n\nFor best practices, see\n[Best Practices for Importing and\nExporting Data](/sql/docs/postgres/import-export).\n\nAfter completing an import operation,\n[verify](/sql/docs/postgres/import-export#verify) the\nresults.\n\nLearn more about the [`pg_dump`](https://www.postgresql.org/docs/12/static/app-pgdump.html), [`pg_dumpall`](https://www.postgresql.org/docs/12/static/app-pg-dumpall.html), and [`pg_restore`](https://www.postgresql.org/docs/12/static/app-pgrestore.html)\nutilities.\n\nExport data from Cloud SQL for PostgreSQL\n-----------------------------------------\n\nYou can use Cloud SQL to perform an export from the Google Cloud console,\nthe [gcloud CLI](/sdk/gcloud), or the API.\n\n- To export a single PostgreSQL database, use the [`pg_dump`](https://www.postgresql.org/docs/12/static/app-pgdump.html) utility.\n- To export all PostgreSQL databases of a cluster, use the [`pg_dumpall`](https://www.postgresql.org/docs/12/static/app-pg-dumpall.html) utility.\n\nWhen using either utility, make sure that you also use the required options to\nensure that the resulting export file is valid for import back into Cloud SQL.\n\n### Export data from an on-premises PostgreSQL server using pg_dump\n\n\nTo export a database that is not managed by Cloud SQL, for later import\ninto Cloud SQL, use the `pg_dump` utility with the following\nflags:\n\n- `--no-owner`\n\n Ownership change commands must not be included in the dump file.\n- `--format`\n\n The `custom` and `directory` formats are allowed if\n the dump file is intended for use with `pg_restore`.\n\n For `plain-text` format, export to a\n [`SQL dump file`](/sql/docs/postgres/import-export/import-export-sql#export-sql-dump-file)\n instead. This format is not compatible with `pg_restore`, and\n must be imported using the Google Cloud console import command or\n `psql` client.\n- `--no-acl`\n\n This flag is required if your dump would otherwise contain statements\n to grant or revoke membership in a `SUPERUSER` role.\n- `--clean`\n\n This optional flag enables you to include the `DROP \u003cobject\u003e` SQL statement that's required to drop (clean) database objects before you import them.\n- `--if-exists`\n\n This optional flag enables you to include the `IF EXISTS` SQL statement with each `DROP` statement that's produced by the `clean` flag.\n\nIn addition, you must remove all of the following:\n\n- Extension-related statements, if Cloud SQL does not support that extension. See [PostgreSQL Extensions](/sql/docs/postgres/extensions) for the list of supported extensions.\n- `CREATE EXTENSION` or `DROP EXTENSION` statements referencing plpgsql. This extension comes pre-installed on Cloud SQL Postgres instances.\n- `COMMENT ON EXTENSION` statements.\n\nConfirm that the default encoding, as determined by the database settings,\nis correct for your data. If needed, you can override the default with the\n`--encoding` flag.\n\n### Export data using the `custom` format from Cloud SQL for PostgreSQL\n\nTo use the custom format, from a command line, run `pg_dump`: \n\n```bash\npg_dump \\\n-U USERNAME \\\n--format=custom \\\n--no-owner \\\n--no-acl \\\nDATABASE_NAME \u003e DATABASE_NAME.dmp\n```\n\n### Export data from multiple files in parallel from Cloud SQL for PostgreSQL\n\nYou can only use the `directory` output format to [export data from multiple files in parallel](/sql/docs/postgres/import-export/import-export-parallel#export-data-multiple-files-parallel).\n\nTo export in parallel, use the `-j `\u003cvar translate=\"no\"\u003eNUM_CORES\u003c/var\u003e flag.\n\u003cvar translate=\"no\"\u003eNUM_CORES\u003c/var\u003e is the number of cores on the source instance.\n\n### Export all databases\n\n[`pg_dumpall`](https://www.postgresql.org/docs/12/static/app-pg-dumpall.html) is\na utility that allows you to extract all PostgreSQL databases of a cluster into\na single script file. This file has SQL commands that you can use to restore the\ndatabases.\n\nTo export all PostgreSQL databases in a Cloud SQL instance, use the\n`pg_dumpall` utility with the following mandatory flags:\n\n- `exclude-database=cloudsqladmin`\n- `exclude-database=template*`\n\nThe `pg_dumpall` utility doesn't have access to the `cloudsqladmin` or\n`template` databases.\n| If you're using the `pg_dumpall` utility to export all PostgreSQL databases of a cluster that aren't managed by Cloud SQL, then you don't have to use the `exclude-database=cloudsqladmin` flag.\n| If an instance has multiple databases and these databases have multiple owners, then this command fails. If this occurs, then use the `pg_dump` utility to export the databases individually.\n\nTo export all PostgreSQL databases, run the following command: \n\n```bash\npg_dumpall \\\n-h HOST_NAME -l DATABASE_NAME --exclude-database=cloudsqladmin \\\n--exclude-database=template* \u003e pg_dumpall.sql\n```\n\nTo view role passwords when dumping roles with `pg_dumpall`, [set the\n`cloudsql.pg_authid_select_role` flag](/sql/docs/postgres/users#setting_the_flags_for_the_pg_shadow_view_and_the_pg_authid_table) to a PostgreSQL role name. If the role exists,\nthen it has read-only (`SELECT`) access to the [`pg_authid`](/sql/docs/postgres/users#pg_shadow) table. This table\ncontains role passwords.\n\nImport\n------\n\nUse the `pg_restore` utility to import an archive into a\nCloud SQL database. `pg_restore` works only with archives\ncreated by [pg_dump](https://www.postgresql.org/docs/current/static/app-pgdump.html) in either the\n`custom` or `directory` formats.\n[Learn more](https://www.postgresql.org/docs/current/static/app-pgrestore.html) about\n`pg_restore`.\n\n### Import from a dump file created with the `custom` format to Cloud SQL for PostgreSQL\n\nIf the dump file was created with custom format, run the following command: \n\n```bash\npg_restore \\\n--list DATABASE_NAME.dmp | sed -E 's/(.* EXTENSION )/; \\1/g' \u003e \u003cvar translate=\"no\"\u003e \u003c/var\u003eDATABASE_NAME.toc\n```\n\nPost-processing from `sed` comments out all extension statements\nin the SQL dump file.\n\nWhen importing using `pg_restore`, specify the processed table of contents with\nthe command-line argument \"--use-list=\u003cvar translate=\"no\"\u003eDATABASE_NAME\u003c/var\u003e.toc\".\n\n### Import data from multiple files in parallel to Cloud SQL for PostgreSQL\n\nYou can [import data from multiple files in parallel](/sql/docs/postgres/import-export/import-export-parallel#import-data-multiple-files-parallel) only for archives created using the `directory` and\n`custom` output formats.\n\nTo import in parallel, use the `-j `\u003cvar translate=\"no\"\u003eNUM_CORES\u003c/var\u003e flag.\n\u003cvar translate=\"no\"\u003eNUM_CORES\u003c/var\u003e is the number of cores on the destination instance.\n\n### Import performance on Cloud SQL for PostgreSQL\n\n| `pg_restore` performance depends on the write performance of the Cloud SQL instance. [Learn more](/sql/docs/postgres/diagnose-issues#performance) about performance in Cloud SQL.\n\nWhat's next\n-----------\n\n- Learn how to [check the status of import and export operations](/sql/docs/postgres/import-export/checking-status-import-export).\n- Learn more about [best practices for importing and exporting data](/sql/docs/postgres/import-export).\n- Learn more about the [PostgreSQL pg_dump utility](https://www.postgresql.org/docs/current/static/app-pgdump.html).\n- [Known issues for imports and exports](/sql/docs/postgres/known-issues#import-export)."]]