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

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*      LISTEN      1448/postgres     
tcp        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.


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.


To get psql prompt if DB is running locally:

$ psql -h -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


1 comment:

micheal pan said...

BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
you can be to get the new PROGRAMMED blank ATM card that is capable of
hacking into any ATM machine,anywhere in the world. I got to know about 
this BLANK ATM CARD when I was searching for job online about a month 
ago..It has really changed my life for good and now I can say I'm rich and 
I can never be poor again. The least money I get in a day with it is about 
$50,000.(fifty thousand USD) Every now and then I keeping pumping money 
into my account. Though is illegal,there is no risk of being caught 
,because it has been programmed in such a way that it is not traceable,it 
also has a technique that makes it impossible for the CCTVs to detect 
you..For details on how to get yours today, email the hackers on : ( ). Tell your 
loved once too, and start to live large. That's the simple testimony of how 
my life changed for good...Love you all ...the email address again is ;