PostgreSQL¶

We're providing PostgreSQL 18.

We update PostgreSQL regularly. You can check the currently installed server version with:

[isabell@moondust ~]$ psql --tuples-only --no-align --command="SHOW server_version;"
18.6

Our setup provides you with a database and a user named like your Asteroid.

[isabell@moondust ~]$ psql -c "\l" | grep "^ $USER"
 isabell     | isabell    | UTF8     | libc            | en_US.UTF-8 | ...

Applications based on PostgreSQL databases will ask you for a username, a password, a database name and possibly a host/port.

Field	Value
Username	equals your username
Password	see below
Database	equals your username
Host/Port	localhost

Your PostgreSQL password differs from any other password. We've created a strong one and put it into the file ~/.pgpass which is used by the psql command-line tool to automatically log you in. Take a look into that file to view your passwords.

Change your password¶

Your password can be changed with the ALTER ROLE SQL statement.

[isabell@moondust ~]$ psql -c "ALTER ROLE ${USER} WITH ENCRYPTED PASSWORD 'your-new-password-g43kfnsak';"
isabell

If you don't see any output, it's a good thing; PostgreSQL only complains if something went wrong.

Update ~/.pgpass after changing the password

It is very important to put the new password into your ~/.pgpass file with a text editor of your choice. That way, command-line tools are still able to automatically log you in.

Read-only user¶

While most applications based on PostgreSQL databases support exactly one database user (and expect it to have write permissions), there are use cases for a read-only user as well, especially from a security perspective. We provide you with a separate user suffixed with _ro ("read-only") which you can use in these cases. This user has a different password than the default read/write user which can also be found in your ~/.pgpass file.

You cannot change the password of the read-only user yourself (it's read-only!). If you really need to change it, please contact our support.

Additional databases¶

In addition to the default database named like your Asteroid, you can create additional databases. Their names must start with your username, for example isabell_blog:

[isabell@moondust ~]$ uberspace tool postgresql database add isabell_blog
OK: Added PostgreSQL database 'isabell_blog' to your Asteroid

You can list your PostgreSQL databases with:

[isabell@moondust ~]$ uberspace tool postgresql database list

  Database
 ──────────────
  isabell
  isabell_blog

To remove a database, use:

[isabell@moondust ~]$ uberspace tool postgresql database del isabell_blog
OK: Removed PostgreSQL database 'isabell_blog' from your Asteroid

Extensions¶

We provide PostGIS 3 for geospatial data. It is enabled automatically in every PostgreSQL database, together with the postgis_topology and postgis_tiger_geocoder extensions. You can check the exact installed PostGIS version with:

[isabell@moondust ~]$ psql --tuples-only --no-align --command="SELECT extversion FROM pg_extension WHERE extname = 'postgis';"
3.6.4

You can also enable additional extensions which PostgreSQL marks as trusted. The available extensions which are not yet enabled in the current database can be listed with:

[isabell@moondust ~]$ psql --tuples-only --no-align --command="SELECT name FROM pg_available_extensions WHERE installed_version IS NULL AND name IN (SELECT name FROM pg_available_extension_versions WHERE trusted) ORDER BY name;"
bool_plperl
btree_gin
btree_gist
citext
cube
dict_int
hstore
intarray
isn
jsonb_plperl
lo
ltree
pg_trgm
pgcrypto
plperl
pltcl
seg
tablefunc
tcn
tsm_system_rows
tsm_system_time
unaccent
uuid-ossp

Extensions are enabled separately for each database. For example, to add pg_trgm to your default database, use:

[isabell@moondust ~]$ psql --command="CREATE EXTENSION pg_trgm;"
CREATE EXTENSION

Missing something?

If you would like to use an extension that is not installed, contact our support, and we will see what we can do.

Automatic backups¶

We back up all your PostgreSQL databases every day at 03:29. You can access the backup files directly in these directories, replacing <asteroid> with your username:

/backup_postgresql/current/<asteroid>/
/backup_postgresql/old/<asteroid>/

There is no uberspace command to create these backups on demand. To create a backup at any time, use pg_dump as described below.

Working with dumps¶

Dumps are the default way of exporting/importing databases. You can use them as a backup or to migrate an existing database dumped on another host to your Asteroid or vice-versa.

Creating dumps¶

The pg_dump command allows you to dump tables or whole databases, represented by a bunch of SQL statements that will re-create the table structures and re-insert all data when executed. The most common use is to redirect its output into a file, like that:

Temporary PostGIS workaround

Due to a known bug, pg_dump currently fails when dumping the PostGIS objects which we install automatically. We will fix this soon. Until then, exclude these objects when dumping a whole database:

[isabell@moondust ~]$ pg_dump --create --clean --no-owner --no-privileges \
  --exclude-table=public.spatial_ref_sys \
  --exclude-schema=tiger \
  --exclude-schema=tiger_data \
  --exclude-schema=topology \
  --exclude-extension=postgis \
  --exclude-extension=postgis_topology \
  --exclude-extension=postgis_tiger_geocoder \
  --exclude-extension=fuzzystrmatch \
  isabell > isabell.sql

Your own tables, including columns using PostGIS types, remain in the dump unless they are stored in one of the excluded schemas. The excluded extensions and schemas already exist in every newly created Uberspace PostgreSQL database.

This command dumps all other tables of the isabell database at once. If you just want to dump a single or a few tables, add the --table option:

[isabell@moondust ~]$ pg_dump --create --clean --no-owner --no-privileges --table=table1 > isabell.table1.sql
[isabell@moondust ~]$ pg_dump --create --clean --no-owner --no-privileges --table=table2 --table=table3 > isabell.table2and3.sql

As the resulting files are plain text files (remember, they are just a bunch of SQL statements) you can easily compress them on the fly, e.g. with xz:

[isabell@moondust ~]$ pg_dump ... | xz > isabell.sql.xz

Importing dumps¶

As dumps are just files containing SQL statements you can feed them into the psql command, importing them into a database of your choice. For example, to import the dump named isabell.sql into your database isabell (overwriting existing tables, if any):

[isabell@moondust ~]$ psql isabell < isabell.sql
SET
[...]

Or in case of a compressed dump, use xzcat to uncompress the data before feeding it into psql:

[isabell@moondust ~]$ xzcat isabell.sql.xz | psql isabell
SET
[...]

External connection¶

For security reasons we don't allow external connections to your databases. However, if you want to connect somehow "directly" from a remote host, you can do so by using an SSH tunnel.

This is how you can initiate an SSH connection offering a tunnel for the PostgreSQL port 5432:

[localuser@localhost ~]$ ssh isabell@moondust.uberspace.de -L 5432:127.0.0.1:5432
[...]
[isabell@moondust ~]$

From now on, you can talk to 127.0.0.1:5432 on your local host to connect to your database. (While in fact, it's OpenSSH listening on port 5432 of your local host, tunneling the connection to your Asteroid.)

Connecting from older Windows versions

When using Windows 8.1 or any other not up-to-date versions of Windows, you will most probably not be able to use SSH as natively as shown here. You will either need to update to a more recent version of Windows or use our howto for Putty DB connection instead of the commands used here.