Getting Started With PostgreSQL

I'm using three prompts to indicate where commands are typed: giles$ or pguser$ for a shell prompt where a user can use the command, root# for a command requiring root access, and postgres=# for a command at the PostgeSQL prompt.

Setup

  • use apt-get, aptitude, or your preferred graphical installer to install "postgresql"
  • Debian install adds group "postgres", which by default has no entries in it
  • edit /etc/group, add yourself to that group
  • log out and back in to force re-evaluation of what groups you're in
  • check by running giles$ groups command
  • giles$ createdb giles gets error createdb: could not connect to database template1: FATAL: role "giles" does not exist even after groups problem corrected
  • according to the documentation: "This will happen if the administrator has not created a PostgreSQL user account for you. (PostgreSQL user accounts are distinct from operating system user accounts.)"
  • so you need to become the "administrator"
  • I initially assumed this was "root" as the account that installed the application, but when I ran root# createuser giles (yes, PG actually owns that command name) ... but I still get createuser: could not connect to database postgres: FATAL: role "root" does not exist
  • a Debian install creates not only a postgres group, but also a "postgres" user - this is better security policy than "root" being the admin
  • the easiest way (at least the way I do things) is to giles$ su - to become root, and then root# su - postgres
  • as postgres/the DB admin you need to run pguser$ createuser giles, which finally succeeds silently
  • now as user giles I run giles$ createdb giles and get a new error: createdb: database creation failed: ERROR: permission denied to create database
  • from the documentation: "If PostgreSQL refuses to create databases for you then the site administrator needs to grant you permission to create databases."
  • to get to a Postgres prompt and start typing SQL commands, run giles$ psql if your user name corresponds to the PostgreSQL username you want, or otheruser$ psql -U <rolename> to assume the role you want
  • use the command postgres=# \q to exit
  • if you're looking at roles as I am now, the SQL command postgres=# select rolname from pg_roles; is helpful - it shows the default postgres user, and the giles user I created earlier
  • note the trailing ";" - it's critical to SQL commands (not the backslash commands)
  • postgres=# \du (inside postgres) likewise lists role information, but with more detail
  • at this point, user "giles" still can't use postgres: the command giles$ psql -U giles is rebuffed with psql: FATAL: database "giles" does not exist
  • SQL: postgres=# create role giles createdb; fails with ERROR: role "giles" already exists
  • it turns out that what you need is SQL: postgres=# alter role giles with createdb;
  • postgres=# \du shows that "giles" now has the role attribute "Create DB"

Basics

  • running giles$ createdb giles at the Bash prompt now works - and takes a perceptible amount of time, a couple seconds perhaps
  • to get to a Postgres prompt and start typing SQL commands, run giles$ psql if your user name corresponds to the PostgreSQL username you want, or otheruser$ psql -U <rolename> to assume the role you want
  • use the command postgres=# \q to exit
  • postgres=# \h in postgres shows you help on SQL commands - you'll need to know some SQL before this is much use
  • slightly more useful (particularly if you're going to be administering the DB) is postgres=# \? which lists psql's internal (backslash) commands (these don't require the trailing semicolon)
  • postgres=# \l lists all databases, including those of other users (presumably there are limitations on this if you're not the primary user)

Backup and Restore

  • there are several methods to back up a database
  • the first discussed is giles$ pg_dump: "An important advantage of pg_dump over the other backup methods described later is that pg_dump's output can generally be re-loaded into newer versions of PostgreSQL, whereas file-level backups and continuous archiving are both extremely server-version-specific. pg_dump is also the only method that will work when transferring a database to a different machine architecture, such as going from a 32-bit to a 64-bit server" (from the documentation)
  • the basic command is giles$ pg_dump database-name > output.file: the output is in text form and can be fed back into psql to recreate the DB
  • an important feature of pg_dump is that it makes consistent backups of the database, even if the DB is currently being written by another user: backing up the entire computer will back up the DBs ... but consistency isn't guaranteed, and is unlikely if they're in use
  • consistency is critical: my default behaviour is to back up the entire hard drive and assume everything in the backup is okay. If a database is actively being written to, this may not be true: thus the use of pg_dump
  • giles$ man pg_dump is useful: it suggests that the best and most flexible options to use are "-Fc" or "-Fd", both of which are compressed by default
  • for the ludicrously small test DB I used, the "compressed" output of "-Fc" was larger than the text dump
  • a larger DB benefits from the compression: a DB with a text dump of 100K becomes a 50K dump with "pg_dump" compression
  • either version can be compressed further, to around 40K, with giles$ gzip -9 <dump-name>
  • backing up while on the database server machine is easiest, but pg_dump also offers the ability to back up a remote machine
  • restoring a text dump: giles$ createdb -T template0 dbname followed by giles$ psql dbname < textdump
  • restores of non-text dumps are done with giles$ pg_restore
  • it appears the actual database(s) are stored in /home/postgres/9.4/main/: there are ~1000 files and folders there, 42M of data ... that's with one added DB with about 10 lines of text above and beyond the base install