You are here

Home › Theatre Manager Online Help › System Setup › Data Utilities

PostgreSQL Terminal Commands

Subscribe to Syndicate
Thu, 05/27/2010 - 09:38 — Darwin

This is a quick reference guide for how to edit settings via the terminal command line.

To download a pdf copy of each OS
Windows Cheatsheet
Mac OS Cheatsheet

Starting/Stopping the Server
Refer to: http://www.postgresql.org/docs/9.0/interactive/app-pg-ctl.html

Mac OS X
  1. Open Terminal
  2. Type su - postgres
  3. Type pg_ctl start or pg_ctl stop or pg_ctl restart
  4. - or - you may need to enter the full pathname of postgresql bin's folder including the location of the data folder if the PATH environment variables are set incorrectly. As in:
    /Library/PostgreSQL/9.0/bin/pg_ctl start -D /Library/PostgreSQL/9.0/data
    /Library/PostgreSQL/9.0/bin/pg_ctl stop -D /Library/PostgreSQL/9.0/data
  5. (Optional) Leave the terminal window open to view stderr log messages as you execute queries against the server.
Windows
  1. Open Control Panel
  2. Open Administrative Tools
  3. Open Services
  4. Find the PostgreSQL Server service
  5. Start, Stop or Restart the service
Reloading Changes to the Configuration File
Refer to: http://www.postgresql.org/docs/9.0/interactive/app-pg-ctl.html - simply sends the postgres process a SIGHUP signal, causing it to reread its configuration files (postgresql.conf, pg_hba.conf, etc.). This allows changing of configuration-file options that do not require a complete restart to take effect.

Mac OS X
  1. Open Terminal
  2. Type su - postgres
  3. Type pg_ctl reload
Windows
  1. Open Command Prompt
  2. Go to C:\Program Files\PostgreSQL\9.0\bin (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd PostgreSQL - enter - cd 9.0 enter - cd bin - enter
  3. pg_ctl reload
Backing Up A Database

Uncompressed backups from this point forward will use the .sql extenstion, while compressed backups will use the .backup extension.


Mac OS X - Uncompressed
  1. Open the terminal
  2. Type su - postgres
  3. Type pg_dump -o [DatabaseName] > [Path] e.g For Database 'Demo' -> pg_dump -o Demo > /Users/Shared/MyBackups/Demo.sql
Mac OS X - Compressed
  1. Open Terminal
  2. Type su - postgres
  3. Type pg_dump -F c -Z 9 [DatabaseName] > [Path] e.g For Database 'Demo' -> pg_dump -F c -Z 9 Demo > /Users/Shared/MyBackups/Demo.backup
Windows - Uncompressed
  1. Open Command Prompt
  2. Go to C:\Program Files\PostgreSQL\9.0\bin (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd PostgreSQL - enter - cd 9.0 enter - cd bin - enter
  3. pg_dump -U postgres -o [DatabaseName] > [Path] e.g For Database 'Demo' -> pg_dump -U postgres -o Demo > /Users/Shared/MyBackups/Demo.sql
Windows - Compressed
  1. Open Command Prompt
  2. Go to C:\Program Files\PostgreSQL\9.0\bin (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd PostgreSQL - enter - cd 9.0 enter - cd bin - enter
  3. pg_dump -U postgres -o -F c -Z 9 [DatabaseName] > [Path] e.g For Database 'Demo' -> pg_dump -U postgres -o -F c -Z 9 Demo > /Users/Shared/MyBackups/Demo.backup
Windows - Compressed (via pgAdmin on a different machine then the server)
  1. Open Command Prompt
  2. Go to C:\Program Files\pgAdmin III\1.12 (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd pgAdmin III - enter - cd 1.12 enter
  3. pg_dump --host [IP address of server] --port 5432 --username "postgres" --format=c --compress=9 --file "C:\BoxOffice\[DatabaseBackupName].backup [DatabaseName] e.g For Database 'Demo' -> pg_dump --host 192.168.0.3 --port 5432 --username "postgres" --format=c --compress=9 --file "C:\BoxOffice\Demo.backup" Demo
Restoring a Database
Mac OS X - Uncompressed
  1. Open Terminal
  2. Type su - postgres
  3. Drop the existing database (If it exists)
  4. Create a new database with the same name
  5. Type psql [DatabaseName] psql Demo
Mac OS X - Compressed
  1. Open Terminal
  2. Type su - postgres
  3. Drop the existing database (If it exists)
  4. Create a new database with the same name
  5. Type pg_restore -d [DatabaseName] [Path] e.g for Database 'Demo' -> pg_restore -d Demo /Users/Shared/MyBackups/Demo.backup
Windows - Uncompressed
  1. Open Command Prompt
  2. Go to C:\Program Files\PostgreSQL\9.0\bin (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd PostgreSQL - enter - cd 9.0 enter - cd bin - enter
  3. Type psql -U postgres [DatabaseName] psql -U postgres Demo
Windows - Compressed
  1. Open Command Prompt
  2. Go to C:\Program Files\PostgreSQL\9.0\bin (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd PostgreSQL - enter - cd 9.0 enter - cd bin - enter
  3. Type pg_restore -U postgres -d [DatabaseName] [Path] e.g for Database 'Demo' -> pg_restore -U postgres -d Demo /Users/Shared/MyBackups/Demo.backup