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 thenroot# 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, orotheruser$ 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, orotheruser$ 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 bygiles$ 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