RESTORE_POSTGRES.md

PostgreSQL Restore Instructions

Based on the analysis of your infra project, here is how your PostgreSQL backup and restore process works.

Your PostgreSQL setup uses Docker Swarm. The database runs as the postgres-1 service in the infra stack, and its data is stored in the Docker volume named infra_postgres-1-data.

The postgres-backup/backup.sh script generates a logical backup using pg_dumpall, which is then compressed into a .sql.gz file and uploaded to Google Cloud Storage. Because this is a logical SQL dump rather than a raw directory backup, restoring it involves wiping the existing database and streaming the SQL statements into a fresh PostgreSQL instance over the Docker overlay network.

Follow these steps on your VPS to safely restore your PostgreSQL data:

1. Locate the backup file on your VPS

Ensure your backup file (e.g., pgdump-YYYY-MM-DD_HH-MM.sql.gz) is present on the VPS. For the commands below, assume it is located in the current directory ($(pwd)) and is named pgdump.sql.gz.

2. Stop dependent services

Before restoring, it is highly recommended to stop any services that connect to PostgreSQL to prevent conflicts or locks while you reset the database. (For example, mqtt-logger).

docker service scale infra_YOUR_SERVICE=0

3. Wipe the existing database volume

To ensure a clean restore without duplicate key errors or schema conflicts, you should start with a fresh database cluster. We will stop Postgres, remove its volume, and start it again so it creates a clean one.

# Stop the PostgreSQL service
docker service scale infra_postgres-1=0

# Wait a few seconds for the container to stop completely, then remove the volume
docker volume rm infra_postgres-1-data

# Start the PostgreSQL service back up
docker service scale infra_postgres-1=1

Note: Wait for about 15-30 seconds after scaling up to ensure PostgreSQL has fully initialized the new database cluster and is ready to accept connections.

4. Restore the database from the SQL dump

Your backup is a compressed SQL script generated by pg_dumpall. We will use a temporary Postgres container attached to the infra_postgres-net overlay network to stream the uncompressed SQL into the newly running Postgres instance.

Run the following command from the directory where your backup is located:

gunzip -c pgdump.sql.gz | docker run --rm -i \
  --network infra_postgres-net \
  -e PGPASSWORD="YOUR_POSTGRES_PASSWORD" \
  postgres:17.4 \
  psql -h postgres-1 -U YOUR_POSTGRES_USER -d postgres

Note: Replace YOUR_POSTGRES_PASSWORD and YOUR_POSTGRES_USER with your actual Postgres credentials (often defined as ${PASSWORD} and ${USERNAME} in your .env file). The default database postgres is used as the entrypoint for pg_dumpall. It is normal to see a few "role already exists" warnings during the restore process.

5. Restart dependent services

Once the restore finishes, start your dependent services back up:

docker service scale infra_YOUR_SERVICE=1

Your PostgreSQL databases, roles, and data should now be fully restored!