Services - ramirezfranciscof/aiida-core GitHub Wiki

PostgreSQL - Management

If you run psql, it will typically attempt to connect to a database with the same name as your user. You can run commands from "outside" like:

$ psql --list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | framirez | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | framirez | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/framirez          +
           |          |          |             |             | framirez=CTc/framirez
 template1 | framirez | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/framirez          +
           |          |          |             |             | framirez=CTc/framirez
(3 rows)

This section contains the commands and tools that are relevant for the handling of databases.

Postgres: creating user and blank databases

The following set of commands will log as postgres user and run psql, then create a user, create a database, and assign all privileges of the database to the user. If you already have a user, you don't need to create a new one. The last lines is just a test.

$ sudo su - postgres
$ psql
# CREATE USER aiida_user WITH PASSWORD '<password>';
# CREATE DATABASE aiida_database OWNER aiida_user ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' TEMPLATE=template0;
# GRANT ALL PRIVILEGES ON DATABASE aiida_database to aiida_user;
# \q
$ psql -h localhost -d aiidadb -U aiida -W

The following commands will (1) set the wrapped format for easier viewing of output (ignore the last . of the upper line and the first . of the lower line for text continuation w/o space, and there is a + for text continuation with space).

username=# \pset format wrapped
username=# \c
username=# \l
username=# \c quicksetup_workuser_10f22b876bd91db3891cf316c71cc4b5

The following comands will list all tables, all sequences (keep track of the last item in table), and all tables and sequences together.

username=# \dt
username=# \ds
username=# \d
username=# SELECT * FROM db_dbuser;

If you want more information about a specific table, you can just run username=# \d+ <TABLE_NAME> (for example, all the columns of the table).

Example query:

SELECT ctime, node_type, attributes
FROM db_dbnode
WHERE node_type like '%CalcFunctionNode%'
ORDER BY ctime DESC
LIMIT 10;

Old Information

Here we show how to list all entries of a database. If it has many columns it might be better to toggle expanded display on for that.

username=# \x on
username=# SELECT * FROM db_dbuser;
username=# \x off

To enter this you need to run:

$ sudo su - postgres
$ psql

You can eventually exit by running:

# \q
$ exit

  1. To list databases use postgres=# \l (before that you can set wrapping of columns to see more clearly: postgres=# \pset format wrapped).

  2. To delete a database use DROP DATABASE <name> ;.

  3. To create a new database use (...?)

PostgreSQL - General

On Ubuntu\Debian, try the pg_lsclusters command to get info on the active postgres clusters and connections.

If you can connect to the database with superuser access, then

SHOW data_directory;

You can also query for it

select setting from pg_settings where name = 'data_directory';

PostgreSQL - Linux

Update and install PostgreSQL 10.4

sudo apt-get update
sudo apt-get install postgresql-10.4

By default, the postgres user has no password and can hence only connect if ran by the postgres system user. The following command will assign it:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"
sudo -u postgres psql -c "CREATE DATABASE testdb;"

Start and stop the PostgreSQL server

sudo service postgresql start
sudo service postgresql stop

PostgreSQL - MacOS

If you installed PostgreSQL via Homebrew:

To start manually:

pg_ctl -D /usr/local/var/postgres start

To stop manually:

pg_ctl -D /usr/local/var/postgres stop

To start PostgreSQL server now and relaunch at login:

brew services start postgresql

And stop PostgreSQL:

brew services stop postgresql

Other Sources to process

⚠️ **GitHub.com Fallback** ⚠️