PostgreSQL

Connection

The connection data depends on the relevant account. You can find the precise values in the administration interface section under Databases > PostgreSQL.

A pgBouncer is running on port 5433. It is possible to use it instead of PostgreSQL directly.

Example with psql

In our example, we use the SSH access and consider the following information:

• Account name: foo
• Database name: foo_base
• We will use the default SSH users and databases, i.e. the ones created when the accounts are opened (e.g. foo for the foo account).

foo@ssh:~$ psql -h postgresql-foo.alwaysdata.net -U foo -W -d foo_base

Permissions

When you create your PostgreSQL databases and users, you define the desired permissions, then our performs the following operations:

• REVOKE ALL PRIVILEGES on the database and its layouts,
• If your user is to have all privileges then:
  • GRANT ALL PRIVILEGES on the database and its layouts,
  • ALTER DEFAULT PRIVILEGES: GRANT ALL PRIVILEGES on TABLES, SEQUENCES and FUNCTIONS,
  • GRANT ALL PRIVILEGES on TABLES, SEQUENCES and FUNCTIONS.
• If your user is to have read only privileges:
  • GRANT CONNECT on the database,
  • GRANT USAGE on the layouts,
  • ALTER DEFAULT PRIVILEGES:
    • GRANT SELECT on TABLES, SEQUENCES,
    • GRANT EXECUTE on FUNCTIONS.
  • GRANT SELECT on TABLES, SEQUENCES,
  • GRANT EXECUTE on FUNCTIONS.

Options

On creating a database, you have the following options:

• local: determines encoding, LC_COLLATE and LC_CTYPE,
• extensions: you can activate PostgreSQL extensions with just a click (hstore, pgcrypto, PostGIS, etc.). If you need an extension that is not listed, you can contact support.
  • plperl is activated on demand via support,
  • pgrouting and postgis_raster are activated on demand via support for PostgreSQL 13 - or higher - databases.

Restore a database from its daily backup

Several options :

• use our backup recovery feature,

• use the following command :

  $ zstdcat $HOME/admin/backup/[date]/postgresql/[database].sql.zst | psql -h postgresql-[account].alwaysdata.net -U [user] -W -d [database]
  

• get the tarbal and use the client of your choice.

Miscellaneous

In Public Cloud, the maximum number of simultaneous connections per user is 50. On request, it is possible to change this for Private Cloud.

It is possible to see the names of all of the databases and users on the PostgreSQL servers. This is a limitation on PostgreSQL usage in a shared environment. Databases contents are not reachable.

Untrusted PostgreSQL languages, which executes arbitrary code with administrator privileges (running PostgreSQL), cannot be used on our servers.

• PostgreSQL documentation