PostgreSQL

Closed Beta

Pukki DBaaS is in closed beta. This means that the service is probably not suitable for most users and there might be breaking changes. If you are still interested in using the service you can contact us to see if the service would be suitable for you.

This documentation provides some hints on how to get started with PostgreSQL and some basic troubleshooting tips. Note that you are responsible for your databases.

Graphical user interface

A popular tool for working with PostgreSQL is pgAdmin that can be found here. Note that the application can not be installed on the database instance, it needs to be installed on your computer or a server that you control. The DBaaS team does not provide support for this application. We are also more comfortable with using the CLI tools.

Command-line

First you need to install the postgresql command line tool. Note that if you are using Linux, your distributions are usually shipped with an ancient version of PostgreSQL, so make sure that you install the most recent major version. For all operating systems you can find instructions for installation here.
Once you have installed the PostgreSQL client you should be able to login into the database. You can find the public IP from the Overview tab or openstack database instance list. The command that you normally want to use from a Linux CLI to connect to your database is:
```
psql --user ${USERNAME} --host ${PUBLIC_IP} ${DATABASE_NAME}
```
The syntax normally used in configuration files is:
```
psql postgresql://${USERNAME}:${PASSWORD}@{PUBLIC_IP}:5432/${DATABASE_NAME}
```
Note that if you use this syntax to login to the database it will not return column names when you do queries.
The most common issues when accessing the database from the CLI are the following:
- If the connection seems to be hanging and you don't get a PostgreSQL prompt it means that either your IP or port is wrong or that you did not create a firewall opening from your host.
Now you should be able to use the database.

How is DBaaS PostgreSQL different from normal PostgreSQL

There are a couple differences from installing PostgreSQL yourself and using DBaaS. Even if you can get admin permissions for the database, it is not recommended. It is better to manage the users and database access from the DBaaS interface. By following these guidelines you'll have a lower risk of shooting yourself in the foot. There is an openstack database root enable command, which can be useful in an education environment if a teacher wants all the students to get admin permissions in their database.

Parameters that users can modify

The DBaaS allows users to modify some of the parameters. If there are some parameters that you think you should be able to modify, please contact us and we'll see if we can make it possible. By default, we assume that default parameters are sane and that users should not, under normal circumstances, modify any of these parameters.

| Parameters | Default | Requires restart | Comments | |:--- |:---:|:---| | maintenance_work_mem | 64MB | No | | | max_connections | 100 | Yes |It is usually recommended to use connection pools instead of modifying this value | | work_mem | 4MB | No | | log_statement | false | No | This is useful if you want to figure out more how your database is utilized | | log_statement_stats | false | No |This will also collect stats from your database, this is recommended to keep as false since it might affect performance |

PostgreSQL extensions

It is not possible for users to add additional extensions that are not already installed. If there are some extensions you would like to see available in Pukki, please be in contact with CSC Service Desk. Note that extensions are not very well tested by the DBaaS-admin.

To enable extensions, you need to first enable root for the database instance and log in as root:
```
openstack database root enable $INSTNACE_ID
```
After you have logged in as root, you can enable the extension of your choice with:
```
CREATE EXTENSION $EXTENSION_NAME
```
After you have enable the extension of your choice, you can log out and disable root:
```
openstack database root disable $INSTANCE_ID
```

Currently available extensions

Name	Comment
moddatetime	functions for tracking last modification time
earthdistance	calculate great-circle distances on the surface of the Earth
pgrowlocks	show row-level locking information
autoinc	functions for autoincrementing fields
dict_int	text search dictionary template for integers
pg_visibility	examine the visibility map (VM) and page-level visibility info
btree_gist	support for indexing common datatypes in GiST
pageinspect	inspect the contents of database pages at a low level
tsm_system_time	TABLESAMPLE method which accepts time in milliseconds as a limit
sslinfo	information about SSL certificates
hstore	data type for storing sets of (key, value) pairs
amcheck	functions for verifying relation integrity
pg_surgery	extension to perform surgery on a damaged relation
intagg	integer aggregator and enumerator (obsolete)
tsm_system_rows	TABLESAMPLE method which accepts number of rows as a limit
dict_xsyn	text search dictionary template for extended synonym processing
old_snapshot	utilities in support of old_snapshot_threshold
isn	data types for international product numbering standards
btree_gin	support for indexing common datatypes in GIN
uuid-ossp	generate universally unique identifiers (UUIDs)
pgstattuple	show tuple-level statistics
xml2	XPath querying and XSLT
pgcrypto	cryptographic functions
pg_freespacemap	examine the free space map (FSM)
intarray	functions, operators, and index support for 1-D arrays of integers
plpgsql	PL/pgSQL procedural language
insert_username	functions for tracking who changed a table
tablefunc	functions that manipulate whole tables, including crosstab
pg_prewarm	prewarm relation data
tcn	Triggered change notifications
postgres_fdw	foreign-data wrapper for remote PostgreSQL servers
dblink	connect to other PostgreSQL databases from within a database
seg	data type for representing line segments or floating-point intervals
lo	Large Object maintenance
adminpack	administrative functions for PostgreSQL
pg_trgm	text similarity measurement and index searching based on trigrams
pg_buffercache	examine the shared buffer cache
citext	data type for case-insensitive character strings
bloom	bloom access method - signature file based index
cube	data type for multidimensional cubes
fuzzystrmatch	determine similarities and distance between strings
unaccent	text search dictionary that removes accents
pg_stat_statements	track planning and execution statistics of all SQL statements executed
refint	functions for implementing referential integrity (obsolete)
ltree	data type for hierarchical tree-like structures
file_fdw	foreign-data wrapper for flat file access

Some useful commands

List databases

\l

List tables

\d

Show table descriptions

\d $TABEL_NAME

Change database

\c $DATABASE_NAME

Note that this is the same command as for creating a new database if it does not exist (and you have given yourself root permissions).

Example query

SELECT row1, row2 FROM table1 ORDER_BY row3 DESC LIMIT 2;

Show all database settings

SHOW ALL;

Show all users

select * from pg_user;

This is also visible from the web interface or the OpenStack CLI. Note that the PostgreSQL user is a service user, i.e. the user that the DBaaS uses to communicate with your database.

Extended display

This will show each column of the record on its own row. This is especially useful when you want to inspect a single record.

SELECT * FROM table1 LIMIT 1 \gx

Import database dump

If you have a database dump, you can import it with the following command. Be aware that this might overwrite what you already have in the database:

psql -h $FLOATING_IP -d $DATABASE -U USERNAME -f file.sql