# Local PostgreSQL Upgrade and Database Restoration Guide

### **PostgreSQL Upgrade and Database Restoration Guide**

To support the [`pgvector` gem](https://github.com/pgvector/pgvector?tab=readme-ov-file), I upgraded my local [Postgres.app](http://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](https://postgresapp.com/)
    
* **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:
    
    ```bash
    pg_dumpall > ~/Desktop/db_backup.sql
    ```
    
2. **Split the Backup File**  
    Extract specific databases from the full backup:
    
    ```bash
    # 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:
    
    ```bash
    rails db:create
    ```
    
4. **Restore Data into Databases**  
    Import the split SQL files into the respective databases:
    
    ```bash
    # 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.