Monday 19 April 2021

PostgreSQL on Ubuntu

Here are some my notes on PostgreSQL on Ubuntu.


To check the PostgreSQL version running:

$ postgres -V

Command 'postgres' not found, did you mean:

  command 'postgrey' from deb postgrey (1.36-5.1)

Try: sudo apt install <deb name>

Let's try to locate binaries:

$ locate bin/postgres
/usr/lib/postgresql/10/bin/postgres
/usr/lib/postgresql/12/bin/postgres

From the ouput above we can see that I have two versions of PostgreSQL installed on my system: 10 and 12. Let's find their full version numbers:

$ /usr/lib/postgresql/10/bin/postgres -V
postgres (PostgreSQL) 10.14 (Ubuntu 10.14-0ubuntu0.18.04.1)

$ /usr/lib/postgresql/12/bin/postgres -V
postgres (PostgreSQL) 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)


PostgreSQL Service


To check if postgres is listening on ports:

$ sudo netstat -ltnp | grep postgres
tcp        0      0 127.0.0.1:5432      0.0.0.0:*      LISTEN      1448/postgres     
tcp        0      0 127.0.0.1:5433      0.0.0.0:*      LISTEN      1456/postgres       

Let's see paths to these processes:

$ sudo pwdx 1448
1448: /var/lib/postgresql/12/main

$ sudo pwdx 1456
1456: /var/lib/postgresql/10/main

So it turned out that both versions of Postgres are currently running in parallel on my system. 


To check the current state of the service:

$ systemctl status postgresql
  postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sun 2021-04-18 21:13:14 BST; 18h ago
   Main PID: 1843 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 38289)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Apr 18 21:13:14 my_computer systemd[1]: Starting PostgreSQL RDBMS...
Apr 18 21:13:14 my_computer systemd[1]: Finished PostgreSQL RDBMS.


To start/stop the service:

$ systemctl start postgresql
$ systemctl stop postgresql

By default (upon installation) postgresql service is configured to start automatically upon computer launch:

$ cat /etc/postgresql/10/main/start.conf
# Automatic startup configuration
#   auto: automatically start the cluster
#   manual: manual startup with pg_ctlcluster/postgresql@.service only
#   disabled: refuse to start cluster
# See pg_createcluster(1) for details. When running from systemd,
# invoke 'systemctl daemon-reload' after editing this file.

auto

The same output is for postgres v12.

To prevent service from starting upon computer reboot:

$ sudo vi /etc/postgresql/10/main/start.conf

...and replace auto with manual.




psql


To get psql prompt if DB is running locally:

$ psql -h 127.0.0.1 -U root -d test-db
Password for user root: 
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 11.2 (Debian 11.2-1.pgdg90+1))
WARNING: psql major version 10, server major version 11.
         Some psql features might not work.
Type "help" for help.

Now we can list all tables:

test-db=# \dt
Did not find any relations.

To quit:

test-db-# \q

TBC...

No comments: