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 16.

Security by default🔗

• LC WEG routinely performs static security scans to identify any container vulnerabilities and update the instances.

PostgreSQL usage tips🔗

PostgreSQL Example Repository

 

WEG maintains a repository with an example Python script for connecting to and using a Postgresql PDS instance.

•   LC PostgreSQL Example 

Below we'll go over a few different ways to connect to your Postgresql instance from LC nodes.

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

[lcuser@lcNode:~]$ psql --version
psql (PostgreSQL) 10.23

This should still allow you to connect with the current version of Postgresql deployed by LaunchIt. If you run into any issues with missing features, contact the LC hotline.

How to connect to PostgreSQL🔗

The command to connect to PostgreSQL is:

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

You will then be prompted for your password, which can be found by going to your workspace in LaunchIt and selecting the Postgresql instance you created.

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>

The file you create should look similar to this:

[foo]
host=test-postgres.apps.czapps.llnl.gov
port=32122
user=testuser
dbname=pgbackuptest
password=mypassword123

Once that file is saved, you can run:

 export PGSERVICECONF=path/to/your/pg_service.conf

Then you can connect to your Postgresql instance with the following command:

 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

test-postgres.apps.czapps.llnl.gov:32122:pgbackuptest:usertest:mypassword123

Which could, for example, be used to connect via

 psql -h test-postgres.apps.czapps.llnl.gov -p 32122 -d psgbackuptest -U postgrestest

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:

[lcuser@lcNode:~]$ psql service=service_name
psql (10.23, server 16.10)
WARNING: psql major version 10, server major version 16.
         Some psql features might not work.

Type "help" for help.

pgtest=> 

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:

pg_dump service=<service name of your choice> > <outputfile>

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

 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:

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

For example,

pg_dump -h test-postgres.apps.czapps.llnl.gov -p 32122 -U postgrestest 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.

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.

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

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

For example:

 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,

 psql -h test-postgres.apps.czapps.llnl.gov -p 32122 -U postgrestest 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.