Postgresql

Postgresql

Authorize external connection

• • Modify /etc/postgresql/12/main/postgresql.conf

listen_addresses = '0.0.0.0/0'

• • Modify /etc/postgresql/12/main/pg_hba.conf

host all all 0.0.0.0/0 md5

• • List active cluster

pg_lsclusters

Change postgres password

sudo -u postgres psql

then

\password postgres

Manage User

• • Create user with login

CREATE ROLE username WITH LOGIN PASSWORD 'mysecret';

• • Provide Superuser to a user

ALTER ROLE username SUPERUSER 

• • List users

select rolname,rolsuper,rolcanlogin FROM pg_roles; 

• • List active cluster

pg_lsclusters

Upgrade

• • List active cluster

pg_lsclusters

• • Stop postgresql service

service postgresql stop 

• • Save database

sudo -u postgres pg_dumpall > backup.sql 

• • Install new postgres version

 apt-get install -y postgresql postgresql-client

• • Install option package

curl -L https://packagecloud.io/timescale/timescaledb/gpgkey | apt-key add -
sh -c "echo 'deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main' > /etc/apt/sources.list.d/timescaledb.list"
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | apt-key add -
apt-get install -y timescaledb-2-postgresql-16 timescaledb-2-postgresql-16

• • Upgrade database

sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \
    --old-datadir=/var/lib/postgresql/14/main \
    --new-datadir=/var/lib/postgresql/16/main \
    --old-bindir=/usr/lib/postgresql/14/bin \
    --new-bindir=/usr/lib/postgresql/16/bin \
    --old-options '-c config_file=/etc/postgresql/14/main/postgresql.conf' \
    --new-options '-c config_file=/etc/postgresql/16/main/postgresql.conf'

PgAdmin4

Url

http://localhost/pgadmin4

Reset Web Password

• Clear /var/lib/pgamdin folder
• Reconfigure the web interface

 dpkg-reconfigure pgamin4-apache2