PSQL

This one will also be another post about configuration since configuring Postgres and its user will always be a pain in the ass for me. Shall we?

Installation

$ sudo pacman -S postgresql

Initial configuration

After the succeeded installation, you can now switch to the postgres user using a privilege elevation program like:

$ sudo su - postgres

Before PostgreSQL can function correctly, the database cluster must be initialized:

[postgres@{machine_name} ~]$ initdb -D /var/lib/postgres/data
# If everything runs successfully you should see this output
The database cluster will be initialized with locales
  COLLATE:  en_US.UTF-8
  CTYPE:    en_US.UTF-8
  MESSAGES: en_US.UTF-8
  MONETARY: pt_BR.UTF-8
  NUMERIC:  pt_BR.UTF-8
  TIME:     pt_BR.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgres/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Fortaleza
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgres/data -l logfile start

Start postgreSQL:

$ sudo systemctl start postgresql

Enable postgreSQL service:

$ sudo systemctl enable postgresql

Check if PostgreSQL is active and running:

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendo>
     Active: active (running) since Sun 2022-02-20 09:54:51 -03; 12s ago
   Main PID: 9057 (postgres)
      Tasks: 7 (limit: 19091)
     Memory: 15.8M
        CPU: 102ms
     CGroup: /system.slice/postgresql.service
             ├─9057 /usr/bin/postgres -D /var/lib/postgres/data
             ├─9059 "postgres: checkpointer "
             ├─9060 "postgres: background writer "
             ├─9061 "postgres: walwriter "
             ├─9062 "postgres: autovacuum launcher "
             ├─9063 "postgres: stats collector "
             └─9064 "postgres: logical replication launcher "

fev 20 09:54:50 ws systemd[1]: Starting PostgreSQL database server...
fev 20 09:54:51 ws postgres[9057]: 2022-02-20 09:54:51.090 -03 [9057] LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.1.0, 64-bit
fev 20 09:54:51 ws postgres[9057]: 2022-02-20 09:54:51.091 -03 [9057] LOG:  listening on IPv6 address "::1", port 5432
fev 20 09:54:51 ws postgres[9057]: 2022-02-20 09:54:51.091 -03 [9057] LOG:  listening on IPv4 address "127.0.0.1", port 5432
fev 20 09:54:51 ws postgres[9057]: 2022-02-20 09:54:51.094 -03 [9057] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
fev 20 09:54:51 ws postgres[9058]: 2022-02-20 09:54:51.100 -03 [9058] LOG:  database system was shut down at 2022-02-20 09:52:34 -03
fev 20 09:54:51 ws postgres[9057]: 2022-02-20 09:54:51.105 -03 [9057] LOG:  database system is ready to accept connections
fev 20 09:54:51 ws systemd[1]: Started PostgreSQL database server.

Create Database in PostgreSQL

To create a valid database, we first need a User. We can take advantage of the user that the system already makes available to us.

$ sudo su - postgres

Secure this default postgres user with a password:

$ psql -c "ALTER USER postgres WITH PASSWORD '{new_password}'"

To test and execute commands, let’s use the PostgreSQL cli. Type:

psql

Run the following command to create a database and then type \l to list the already created databases:

CREATE DATABASE mydb;

Create an User in PostgreSQL

If you want a more assisted way, run this command:

createuser --interactive --pwprompt

If not, you can run those two to create a User with a password defined and a proper role for it:

CREATE USER {user_name} WITH ENCRYPTED PASSWORD '{user_password}';

GRANT ALL PRIVILEGES ON DATABASE {db_name} TO {username};

Useful commands to run when using PSQL

Login in to your user, run the psql command, and type:

• Connect to a database:

  \c {db_name} {username}

• List available databases:

  \l

• List available tables:

  \dt or \dt+

• Describe a table:

  \d {table_name}

• List available schema:

  \dn

• List available functions:

  \df

• List available views:

  \dv

• List users and their roles:

  \du

• Execute the previous command (psql executes the previous command again, which is the SELECT statement):

  SELECT version();

  \g

• Command history:

  \s

• Execute psql commands from a file:

  \i {filename}

• Turn on query execution time:

db_name=# \timing
Timing is on.
db_name=# SELECT COUNT(*) FROM table;
 count
-------
  1000
(1 row)

Time: 0.495 ms
db_name=#

• Edit command in your own editor. It will open the text editor defined in the EDITOR environment variable:

  \e