As a full-stack developer, managing database backups is crucial for testing, development, and disaster recovery. pgAdmin, a popular GUI tool for PostgreSQL, simplifies backing up and restoring databases without needing command-line tools like pg_dump or pg_restore. This guide walks you through the process, using a real-world example of backing up a production database and restoring it locally or on a staging server.

Prerequisites

• pgAdmin 4: Installed on your local machine (download here).
• PostgreSQL: Installed locally if restoring to your machine.
• Database Access: Credentials for your database (e.g., postgresql://lms_user:[email protected]:5432/lms_db).
• Firewall: Ensure port 5432 is open on the database server for pgAdmin access.

Step 1: Back Up the Database

1. Connect to Your Database:

   • Open pgAdmin and register your server:
     • Right-click “Servers” > “Register” > “Server”.
     • Enter a name (e.g., “Production”).
     • In the “Connection” tab, add:
       • Host: 173.249.42.82 (or your server’s IP).
       • Port: 5432.
       • Database: lms_db (your database name).
       • Username: lms_user.
       • Password: password.
     • Save and connect.

2. Create the Backup:

   • Navigate to “Servers” > “Production” > “Schemas” > “public” > “Databases” > lms_db.
   • Right-click lms_db > “Backup”.
   • In the “Backup Database” dialog:
     • Filename: Choose a local path (e.g., /Users/yourname/prod_backup.sql or C:\Users\yourname\prod_backup.sql).
     • Format: Select “Custom” for flexibility.
     • Compression: Enable for a smaller file (e.g., prod_backup.sql.gz).
     • Options: Include “CREATE DATABASE” for full restores or exclude sensitive tables (e.g., users) in the “Objects” tab.
   • Click “Backup” and monitor the process in the “Process Watcher”.

3. Verify the Backup: Check the file in your local directory:

   ls -lh /Users/yourname/prod_backup.sql.gz  # macOS/Linux
   dir C:\\Users\\yourname\\prod_backup.sql.gz   # Windows
   

Step 2: Restore the Database

1. Set Up the Target Database:
   • For local testing, create a new database in pgAdmin:
     • Connect to your local PostgreSQL server (e.g., localhost:5432).
     • Right-click “Databases” > “Create” > “Database”.
     • Name it (e.g., lms_db_local) and save.
   • For a staging server, connect to it in pgAdmin or transfer the file via scp.
2. Restore the Data:
   • Right-click the target database (lms_db_local) > “Restore”.
   • In the “Restore Database” dialog:
     • Filename: Select prod_backup.sql (or .sql.gz if compressed).
     • Format: Choose “Custom or tar”.
     • Options: Enable “Pre-data”, “Data”, and “Post-data” for a full restore.
   • Click “Restore” and monitor progress.
3. Verify the Restore:

   • In pgAdmin, expand the database > “Schemas” > “public” > “Tables” to view tables.

   • Run a query to check data:

     SELECT * FROM some_table LIMIT 5;
     

Step 3: Integrate with Your App

For a Prisma-based Node.js app:

• Update your schema.prisma to match the restored database:

  npx prisma db pull
  

• Generate the Prisma client:

  npx prisma generate
  

• Update .env with the target database URL:

  DATABASE_URL="postgresql://postgres:your_password@localhost:5432/lms_db_local?schema=public"
  

• Run your app:

  npm run start:dev