PostgreSQL¶

We're providing PostgrSQL 18.1.

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.

Warning:

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¶

You currently cannot create new databases because PostgreSQL does not allow us to give you permission to create datbases limited to some naming scheme, like mariadb does. If that's something you need, please contact our support, tell them that you would like us to include this as a feature and tell them what you would like to do with it.

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:

[isabell@moondust ~]$ pg_dump --create --clean --no-owner --no-privileges isabell > isabell.sql

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

[isabell@moondust ~]$ pg_dump --create --clean --no-owner --no-privileges -t table1 > isabell.table1.sql
[isabell@moondust ~]$ pg_dump --create --clean --no-owner --no-privileges -t table2 -t 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

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

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

Tip:

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.