Local PostgreSQL Upgrade and Database Restoration Guide

PostgreSQL Upgrade and Database Restoration Guide

To support the pgvector gem, I upgraded my local Postgres.app from v13 to v17 on macOS. Below is the full upgrade and restoration process for my project.

Environment

• OS: macOS

• PostgreSQL: Upgraded from v13 to v17 via Postgres.app

• Rails: Used for database management

• Databases: my_database_name_development, my_database_name_test

---

Steps

1. Back Up All Databases
   Export all databases to a single SQL file:

    pg_dumpall > ~/Desktop/db_backup.sql
   

2. Split the Backup File
   Extract specific databases from the full backup:

    # Development
    awk '/^\\connect my_database_name_development/{flag=1;next} /^\\connect/{flag=0} flag' ~/Desktop/db_backup.sql > ~/Desktop/my_database_name_development_only.sql
   
    # Test
    awk '/^\\connect my_database_name_test/{flag=1;next} /^\\connect/{flag=0} flag' ~/Desktop/db_backup.sql > ~/Desktop/my_database_name_test_only.sql
   

3. Create New Databases
   Recreate the databases using Rails:

    rails db:create
   

4. Restore Data into Databases
   Import the split SQL files into the respective databases:

    # Development
    psql -d my_database_name_development -f ~/Desktop/my_database_name_development_only.sql
   
    # Test
    psql -d my_database_name_test -f ~/Desktop/my_database_name_test_only.sql
   

---

This process ensures a clean PostgreSQL upgrade while preserving all development and test data.