PostgreSQL - Local Installation

Installation

Postgres client and server programs are packaged separately, with different naming conventions per OS.

OS X

Ubuntu 16

Package postgresql is the Postgres server and postgresql-client is the client.

$ sudo apt-get install postgresql postgresql-client

Check the server status with

$ service postgresql status

Fedora 23

Postgres is available in the main repository (9.4.8-1.fc23, i.e. Postgres 9.4, as of July 2016). There are three packages of interest:

postgresql
postgresql-server
postgresql-contrib

posgresql is the Postgres client package. posgresql-server is the Postgres server package. postgresql-contrib is a package of extension modules that most installation guides recommend, though I haven't found it necessary for our purposes (Joel). Install the first two with

$ sudo dnf install posgresql postgresql-server

You can check the status of the Postgres server with

$ sudo systemctl -l status postgresql

Immediately after installation, you'll find no server process will be active.

Initialization

A "unit" of Postgres comprises

1) A Postgres database cluster

2) A data directory to store data associated with that cluster

3) An instance of the Postgres server process to serve data from that cluster

Any one of these three things requires the other two.

The installation process may or may not initialize a new cluster, depending mostly on OS. If you don't have a postgres server process running after installation, the first thing you'll want to do is initialize a cluster. We haven't found this necessary on OS X or Ubuntu so far.

Fedora 23

Initialize with

$ sudo postgresql-setup --initdb

This will create the configuration files postgresql.conf and pg_hba.conf, among other functions.

Now start the server process with

$ sudo systemctl start postgresql

If everything goes well, you won't get output. Check the status again if you want to make sure it worked.

Load backups

The databases on i2u2-db are owned by Postgres role root, and the test databases by role portal2006_1022. It's easiest to create the appropriate role on your own installation before loading the backups. From within psql,

# CREATE USER (root | portal2006_1022);

Create backup files from the databases on i2u2-db using pg_dump (see the Postgres usage page), then scp them (or rsync, your choice) over to your local system:

$ scp username@i2u2-db.crc.nd.edu:/home/<username>/backup_file.bak ~/backup_file.bak

(for example)

The quickest way to restore the backups is with the command

$ sudo -u postgres pg_restore -C -d postgres <backup filename>

• Since you're acting as the OS user postgres, that user will need permissions to access the backup file. This means you'll probably need to have it outside of your home folder.
• The -C flag tells pg_restore to create a new database to restore the backup into. The name of the new database will always be the name of the old database, which is stored in the backup.
• For some technical reason, pg_restore must access an existing Postgres database in order to issue the correct commands, even if it's creating a new database. The -d flag specifies the name of an existing database, in this case the "postgres" database that's created with every installation. You could use any existing database on your local system in place of "postgres." Whichever existing database you choose, it will not be touched by this process!
• During restoration, pg_restore will look for a Postgres role that matches the role that owned the original database (most likely portal2006_1022 for our db's). You'll get a bunch of errors if it doesn't find it, but that doesn't seem to affect the creation of the new database or its contents.

If you prefer, you can also create the new database independently, and then load the backup into it with

$ sudo -u postgres pg_restore -d <database name> <backup filename>

Do this if you want your local database to have a different name from the original.

-- Main.JoelG - 2016-07-01

Comments