Skip to content

PostgreSQL

PostgreSQL is an open-source relational database management system. Management is available from admin panel, phpPgAdmin and after logging in with command: devil pgsql.

Warning

The PostgreSQL server address depends on the server number. For server s0.serv00.com will be pgsql0.serv00.com, for server s1.serv00.com - pgsql1.serv00.com, for server s2.serv00.com - pgsql2.serv00.com etc. The examples use pgsqlX.serv00.com

DevilWEB

After logging in to the admin panel PostgreSQL database management is in the PostreSQL tab. After clicking, a list of databases is shown. You can install extensions and change the password for each database separately.

PostgreSQL databases - list of databases

New database

To add a new database, click + Add database and fill in its name and password on the next page.

PostgreSQL databases - adding a database

Devil

PostgreSQL configuration is also possible from shell level using the devil pgsql module.

Adding and removing a database

Adding a PostgreSQL database is done with the command devil pgsql db add DATABASE_NAME, where DATABASE_NAME is the name of the database. A user with the same login as the database name will be created automatically. After executing this command, you will be asked for a password.

Deleting the PostgreSQL database is done with the command: devil pgsql db del DATABASE_NAME

User password change

Changing the password is done with the command: devil pgsql passwd DATABASE_NAME.

Extensions

To enable the extension for the selected database, use the command: devil pgsql extensions DATABASE_NAME EXTENSION, where:

  • DATABASE_NAME - PostgreSQL database name
  • EXTENSION - extension name

List of available extensions:

  • ltree
  • pg_trgm
  • hstore
  • postgis
  • postgis_topology
  • pgcrypto
  • uuid-ossp
  • cube
  • earthdistance
  • unaccent

List of databases and users

To get a list of all databases and users use the command: devil pgsql list

Remote access

To remotely log in to the PostgreSQL database, use SSH tunneling. In the example below, the port 5432 of the pgsqlX.serv00.com is forwarded to local port 8543 by sX server.serv00.com using SSH. This makes the PostgreSQL server available on the local port.

ssh -f LOGIN@sX.serv00.com -L 8543:pgsqlX.serv00.com:5432 -N psql -h localhost -p 8543 -U POSTGRESQL_USER -W

To add tunneling in the Putty client, after configuring the connection, go to the Connection/SSH/Tunnels tab, and then:

  • fill in Source port - 8543
  • fill in Destination - pgsqlX.serv00.com:5432
  • select Local and Auto.

Then you need to click the Add button. After that, you can connect to the SSH server. The PostgreSQL server will be available on localhost and port 8543.