如何通过pg_dump/pg_restore迁移PostgreSQL数据库时区设置

Ah, I've run into this exact issue before! The problem here is that when you use ALTER DATABASE mydatabase SET TimeZone = 'MST'; to set a database-level parameter, those custom settings aren't included in a standard pg_dump backup by default. That's why after restoring, your TimeZone resets to localtime—the backup didn't capture that database-specific configuration.

Here are three reliable ways to fix this:

1. Use pg_dump with the --create flag (Simplest Approach) #

The --create flag tells pg_dump to include the full CREATE DATABASE statement along with all database-level configuration settings (like your TimeZone value) in the backup.

Backup Command: #

pg_dump --create -d mydatabase -f mydatabase_backup.sql

• --create: Adds the CREATE DATABASE statement and applies all database-level settings
• -d mydatabase: Specifies the target database to backup
• -f mydatabase_backup.sql: Saves the backup to a file

Restore Command: #

If you're using plain SQL backup:

psql -U your_username -f mydatabase_backup.sql postgres

(Note: Use the postgres database as the connection target here, since the backup will create mydatabase from scratch)

If you're using a custom format backup (recommended for larger databases):

# Backup first with custom format
pg_dump --create -Fc -d mydatabase -f mydatabase_backup.dump

# Restore
pg_restore -U your_username --create -d postgres mydatabase_backup.dump

2. Manually Export & Reapply Database-Level Settings #

If you don't want to recreate the entire database (e.g., restoring into an existing database), you can extract the current database settings and run them after restoring the data.

Step 1: Export the Settings #

Run this query in psql connected to mydatabase:

SELECT 'ALTER DATABASE ' || quote_ident(current_database()) || ' SET ' || name || ' = ' || quote_literal(setting) || ';'
FROM pg_settings
WHERE source = 'database';

This will generate ALTER DATABASE statements for all database-level parameters (including your TimeZone setting). Copy these statements to a file (e.g., db_settings.sql).

Step 2: Restore Data & Apply Settings #

First restore your standard backup:

pg_restore -U your_username -d mydatabase mydatabase_backup.dump

Then apply the saved settings:

psql -U your_username -d mydatabase -f db_settings.sql

3. Use pg_dumpall for Full Server-Level Backup #

If you need to backup all databases and global server settings (including database-level configurations), use pg_dumpall. This is overkill for a single database, but useful if you're migrating an entire server.

Backup Command: #

pg_dumpall -U your_username -f full_server_backup.sql

Restore Command: #

psql -U your_username -f full_server_backup.sql postgres

Important Notes: #

• Always test your backup/restore workflow in a staging environment first to avoid surprises in production.
• Database-level settings take precedence over user-level (ALTER USER ... SET) and server-level (postgresql.conf) settings, so once applied, they'll be the default for all connections to that database.

内容的提问来源于stack exchange，提问作者Michal Špondr