如何在Docker中通过卷恢复PostgreSQL数据库及解决镜像启动后数据为空问题

Hey there! Let’s tackle both your PostgreSQL Docker issues step by step—restoring from a dump/SQL file with volumes, and figuring out why your pre-built image is spinning up an empty database.

There are two common scenarios here: restoring to a running container, or setting up a new container with pre-loaded data.

Scenario 1: Restore to an Already Running Container #

If your PostgreSQL container is already up and using a volume for data persistence:

• First, copy your dump file into the container:

  docker cp /path/to/your/local/dump.sql your-postgres-container-name:/tmp/dump.sql
  

• Then run the restore command directly via docker exec. For plain SQL files:

  docker exec -it your-postgres-container-name psql -U your-db-username -d your-target-db -f /tmp/dump.sql
  

• If you have a custom-format dump (from pg_dump -Fc), use pg_restore instead:

  docker exec -it your-postgres-container-name pg_restore -U your-db-username -d your-target-db -v /tmp/dump.dump
  

• Pro tip: If you hit permission errors, adjust the file ownership inside the container first:

  docker exec your-postgres-container-name chown postgres:postgres /tmp/dump.sql
  

Scenario 2: Restore When Creating a New Container #

The PostgreSQL official image has a handy feature: any SQL/dump files placed in /docker-entrypoint-initdb.d/ will automatically run when the container starts for the first time (when the data volume is empty). Here's how to use it:

1. Create a dedicated volume for your PostgreSQL data (optional but recommended):

   docker volume create postgres-persistent-data
   

2. Run the container, mounting both your dump file and the data volume:

   docker run -d \
     --name new-postgres-container \
     -e POSTGRES_USER=your-db-user \
     -e POSTGRES_PASSWORD=your-db-pass \
     -e POSTGRES_DB=your-target-db \
     -v postgres-persistent-data:/var/lib/postgresql/data \
     -v /path/to/your/local/dump.sql:/docker-entrypoint-initdb.d/dump.sql \
     postgres:latest
   

The image will handle creating the database (if it doesn't exist) and running your dump file automatically.

If your pre-built image is supposed to have data but spins up an empty database, here are the most likely fixes:

Check for Volume Overwrites #

The #1 culprit is accidentally mounting an empty external volume over the image's built-in data directory (/var/lib/postgresql/data). When you run:

docker run -v new-empty-volume:/var/lib/postgresql/data your-prebuilt-postgres-image

You're replacing the image's pre-loaded data with an empty volume. Instead:

• Run the image without mounting an external volume first to verify the data exists:

  docker run --name test-container your-prebuilt-postgres-image
  

  If the data shows up here, you need to copy the image's data to your volume first (see next step).

Copy Image Data to Your Persistent Volume #

If you need to use a persistent volume but want to keep the image's data:

1. Start a temporary container from your image (no external volume mounted):

   docker run --name temp-data-container your-prebuilt-postgres-image
   

2. Wait a few seconds for the database to initialize, then stop the container (don't delete it yet):

   docker stop temp-data-container
   

3. Create your target persistent volume:

   docker volume create your-persistent-volume
   

4. Copy the data from the temporary container to the volume:

   docker cp temp-data-container:/var/lib/postgresql/data/. your-persistent-volume:/
   

5. Clean up the temporary container:

   docker rm temp-data-container
   

6. Now run your container with the pre-populated volume:

   docker run -v your-persistent-volume:/var/lib/postgresql/data your-prebuilt-postgres-image
   

Avoid Overriding Environment Variables #

If your pre-built image has a pre-configured database, don't specify POSTGRES_DB, POSTGRES_USER, or POSTGRES_PASSWORD in your docker run command. These variables trigger a fresh database initialization, which will overwrite the image's existing data with an empty database.

内容的提问来源于stack exchange，提问作者Yesha Dave