PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. 
[PostgreSQL Official Documentation](https://www.postgresql.org/docs/)

(WE G)ot PostgreSQL🔗

• Workflow Enablement Group (WEG) is proud to offer PostgreSQL hosted as a Docker container in our new LC Kubernetes/Openshift cluster.
• We currently support version 12.

Security by default🔗

• LC WEG routinely performs static security scans powered by Clair in order to find any container vulnerabilities.
• TLS/SSL connections are enforced and required.

PostgreSQL usage tips🔗

Getting an updated version of PostgreSQL🔗

You may notice that the default installation of PostgreSQL on LC systems is a bit outdated

janeh@oslic5:~$ psql --version
psql (PostgreSQL) 9.2.24

In the examples below, we'll be working with an updated version of PostgreSQL via Singularity. First, pull our latest version of PostgreSQL from our Quay repository via

singularity pull my_postgres.sif docker://quay.apps.czapps.llnl.gov/lcweg/lc-postgres-12:iz-latest

This will create the file my_postgres.sif in your working directory. You can see the version of PostgreSQL living in this container via

janeh@oslic5:~$ singularity exec my_postgres.sif psql --version
psql (PostgreSQL) 12.6 (Debian 12.6-1.pgdg100+1)

Since we'll be working with a container to use PostgreSQL binaries, you may want to refer to our docs on containers.

How to connect to PostgreSQL🔗

The command to connect to PostgreSQL is

psql -h <hostname> -p <port> -d <dbname> -U <username>

However, to invoke the version of psql living in my_postgres.sif, you'll want to either precede this command with singularity exec my_postgres.sif as in

singularity exec my_postgres.sif psql -h <hostname> -p <port> -d <dbname> -U <username>

or open up a shell into the container prior to running psql -h <hostname> -p <port> -d <dbname> -U <username>:

singularity shell my_postgres.sif
Singularity> psql -h <hostname> -p <port> -d <dbname> -U <username>`

By default, either approach will cause PostgreSQL to prompt you for your password.

Connection service files🔗

Connecting to your database is much easier with a connection service file. The file, $HOME/.pg_service.conf, should have the following format:

[<service name of your choice>]
host=<hostname>
port=<port>
user=<username>
dbname=<database>
password=<your_password>

For example, for the connection information in previous sessions, I might use the connection service file, ~/.pg_service.conf, containing

[foo]
host=janeha-postgres.apps.cztest.llnl.gov
port=32122
user=janetest
dbname=psgbackuptest
password=mypassword123

I can then connect by simply running psql service=foo after opening a shell into a singularity container containing postgreSQL or, from outside the container, singularity exec my_postgres.sif psql service=foo. More generally this syntax is

singularity exec my_postgres.sif psql service=<service name of your choice>

Passing your password automatically with .pgpass🔗

Another option to avoid being prompted for a password via a file called .pgpass in your home directory containing the following info:

hostname:port:database:username:password

For example, ~/.pgpass might contain

janeha-postgres.apps.cztest.llnl.gov:32122:psgbackuptest:janetest:mypassword123

Which could, for example, be used to connect via

singularity exec my_postgres.sif psql -h janeha-postgres.apps.cztest.llnl.gov -p 32122 -d psgbackuptest -U janetest

Note that unlike with a connection service file, using .pgpass requires that you specify at least the port, database name, and hostname on the command line.

Successful connections🔗

Once you've connected to your database successfully, you should see a message from psql and command line prompt preceded by the name of your database, as in the following, which relies on a connection service file:

janeh@flash21:~/myworkspace/PDS$ singularity exec my_postgres.sif psql service=foo
psql (12.7 (Debian 12.7-1.pgdg100+1), server 12.6 (Debian 12.6-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

psgbackuptest=#

How to back up a database to a .sql file🔗

You can dump a database to a .sql file using pg_dump. If you have set up a connection service file, as above, you'll just need to specify the name of the service (specified in the connection service file) and the name of the output file where you would like to store the backup, as in the following:

singularity exec <psql container image> pg_dump service=<service name of your choice> > <outputfile>

For service, foo and output file psgbackuptest_dump.sql, this might look like

singularity exec my_postgres.sif pg_dump service=foo > psgbackuptest_dump.sql

If you're instead relying on .pgpass, you can run pg_dump without authenticating but will again need to give more information about your database, with the following syntax:

singularity exec <psql container image> pg_dump -h <hostname> -p <port> -U <username> <database> > <outputfile>

For example,

singularity exec my_postgres.sif pg_dump -h janeha-postgres.apps.cztest.llnl.gov -p 32122 -U janetest psgbackuptest > psgbackuptest_dump.sql

This creates the <outputfile>, psgbackuptest_dump.sql. Check that this file has been created and that it contains information about the database you've just dumped, as expected.

Note that if you've already opened a shell into your container via, for example, singularity shell my_postgres.sif, you can run pg_dump directly with the syntax

pg_dump -h <hostname> -p <port> -U <username> <database> > <outputfile>

How to restore a database🔗

To restore a database from a .sql file, you'll use the command psql either after shelling into a container or on the same line as singularity exec <psql container image>.

If you're using a connection service file, you will want to specify the service and the input file storing the backup via

singularity exec <psql container image> psql service=<service name> < <input file>

outside the container or

psql service=<service name> < <input file>

inside the container. For example, via

singularity exec my_postgres.sif psql service=foo < psgbackuptest_dump.sql

or

psql service=foo < psgbackuptest_dump.sql

Again, if you're using .pgpass instead of a connection service file, you'll also need to specify the hostname, port, username, and database name. For example,

singularity exec my_postgres.sif psql -h janeha-postgres.apps.cztest.llnl.gov -p 32122 -U janetest psgbackuptest < psgbackuptest_dump.sql

Note that the direction of the arrow, <, points in opposite directions, depending on whether you are dumping or restoring a database.

Be careful about overwriting newer content when you import a backup, especially if you have multiple tables dumped into the .sql file.