Friday, December 5, 2014

PostgreSQL: wipe all databases and start with a clean slate

Scenario: I munged the 'postgres' database, or I DROPped the 'template1' database, or I did some other similar stupidity. I don't have any important data in my PostgreSQL setup, so I would just like to wipe all the databases and start afresh.

Procedure:

  1. As root, shut down the PostgreSQL server.
    # systemctl stop postgresql.service
    
  2. Become the postgres user.
    # su - postgres
    
  3. Copy the pg_hba.conf file to postgres home directory so I can restore it later.
    postgres$ cp data/pg_hba.conf .
    
  4. Wipe the /var/lib/pgsql/data/ directory.
    postgres$ rm -rf /var/lib/pgsql/data/
    
  5. Become root again.
    postgres$ exit
    
  6. Start the server again - it will detect that the /var/lib/pgsql/data/ directory is missing and re-create it.
    # systemctl start postgresql.service
    
  7. Become the postgres user.
    # su - postgres
    
  8. Resore the pg_hba.conf file to data/
    postgres$ cp pg_hba.conf data/
    
  9. Become root again.
    postgres$ exit
    
  10. Restart the server to load the production pg_hba.conf settings.
    # systemctl restart postgresql.service