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 [email protected] -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.