Skip to main content

PostgreSQL

Install​

Install 18.x, Ubuntu 24.04 pass.

# noble (24.04, LTS)
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

. /etc/os-release
sudo sh -c "echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main' > /etc/apt/sources.list.d/pgdg.list"

sudo apt update
sudo apt install postgresql-18

Install 15.x, Ubuntu 22.04 pass.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-key list

sudo apt-key export ACCC4CF8 | sudo gpg --dearmour -o /usr/share/keyrings/pgdg.gpg

# jammy (22.04, LTS)
# Remove the old data, add the new one
# deb [arch=amd64 signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main
sudo vi /etc/apt/sources.list.d/pgdg.list

sudo apt -y update

sudo apt -y install postgresql-15

systemctl status postgresql

sudo apt-key del ACCC4CF8

Ubuntu 20.04 pass, default 12.x. Ubuntu 18.04 pass.

# Check available version.
sudo apt-cache search postgresql | grep postgresql

sudo apt install postgresql postgresql-contrib

# Check service.
systemctl status postgresql.service

# Check version.
sudo -u postgres psql -c "SELECT version();"

# N: Skipping acquire of configured file 'main/binary-i386/Packages' as repository 'http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' doesn't support architecture 'i386'
sudo vi /etc/apt/sources.list.d/pgdg.list
# Modify to : deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt focal-pgdg main

Install 14.x, Ubuntu 20.04 pass.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-key list

sudo apt -y update

sudo apt -y install postgresql-15

systemctl status postgresql

Operation​

# Setting linux user password, not database user's password
sudo passwd postgres

# Search for all the package names installed that contain the sub-string postgres.
dpkg -l | grep postgres

# Uninstall
sudo apt-get --purge remove postgresql
sudo apt-get purge postgresql*
sudo apt-get --purge remove postgresql postgresql-doc postgresql-common

sudo apt-get --purge remove {POSTGRESS-PACKAGE NAME}

ls /var/lib | grep postgresql
ls /var/log | grep postgresql
ls /etc | grep postgresql
sudo rm -rf /var/lib/postgresql/
sudo rm -rf /var/log/postgresql/
sudo rm -rf /etc/postgresql/

# Run SQL Script
setfacl -x u:postgres /root/service/backend/prod-oauth-postgresql.sql
sudo -u postgres psql postgres -h 127.0.0.1 -d elecdove -f /root/service/backend/prod-oauth-postgresql.sql

Upgrade​

# Upgrade from 12.x -> 14.x
# If only 1 instance, install 14.x directly, will auto upgrade.

Remote access​

Check.

# confirm instances
pg_lsclusters

grep listen /etc/postgresql/18/main/postgresql.conf
grep listen /etc/postgresql/15/main/postgresql.conf # 15.x

Modify.

sudo vi /etc/postgresql/18/main/postgresql.conf
sudo vi /etc/postgresql/15/main/postgresql.conf # 15.x

Change to:

# listen_addresses = 'localhost'		# what IP address(es) to listen on;
listen_addresses = '*'
sudo vi /etc/postgresql/18/main/pg_hba.conf # 18.x
sudo vi /etc/postgresql/15/main/pg_hba.conf # 15.x

Append:

host    all             all             192.168.0.0/16          md5
# For DEV.
host    all             all             0.0.0.0/0               md5
# For WAN.
host    all             all             180.0.0.0/8             md5
host    all             all             162.0.0.0/8             md5
hostssl all             all             116.89.139.110/32       md5  # Allow Static IP with ssh.
scram-sha-256

peer : only supported on local connections.

password : supply an unencrypted password, fast but danger.

md5 : Perform SCRAM-SHA-256 or MD5 authentication to verify the user's password.

# Apply
sudo /etc/init.d/postgresql restart
sudo systemctl reload postgresql.service

# Verify that the service is listening on port 5432.
ss -tunelp | grep 5432

Connect​

# Option 1.
# Running the Postgres command directly with sudo.
sudo -u postgres psql

# Option 2.
# For this option, switch to the Postgres user created after PostgreSQL installation.
sudo -i -u postgres
psql

# Example : connect to remote database
psql -h 192.168.0.50 -U user1 -d database1

Postgresql : Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

The error you quote has nothing to do with pg_hba.conf; it's failing to connect, not failing to authorize the connection.

Connect SSL​

pgsql A 10800 172.105.222.93 sudo certbot certonly --standalone -d pgsql.elecdove.com

Problem binding to port 80: Could not bind to IPv4 or IPv6.

service nginx stop

service nginx start

sudo -u postgres psql -U postgres -c 'SHOW data_directory'

/var/lib/postgresql/14/main

sudo vi /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy
#!/bin/bash
umask 0177
DOMAIN=elecdove.com # ls /etc/letsencrypt/live
DATA_DIR=/var/lib/postgresql/14/main
cp /etc/letsencrypt/live/$DOMAIN/fullchain.pem $DATA_DIR/server.crt
cp /etc/letsencrypt/live/$DOMAIN/privkey.pem $DATA_DIR/server.key
chown postgres:postgres $DATA_DIR/server.crt $DATA_DIR/server.key
sudo chmod +x /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy

sudo -u postgres psql -U postgres -c 'SHOW config_file'

# /etc/postgresql/14/main/postgresql.conf

sudo vi /etc/postgresql/14/main/postgresql.conf
ssl = on  
ssl_cert_file="server.crt"  
ssl_key_file="server.key"  
ssl_prefer_server_ciphers = on
sudo -u postgres psql -U postgres -c 'SHOW config_file'

# /etc/postgresql/14/main/postgresql.conf

sudo vi /etc/postgresql/14/main/pg_hba.conf
hostssl all all 0.0.0.0/0 md5

# Static IP
hostssl all all 122.117.139.25/32 md5
# Renew and load hook
sudo certbot renew --force-renewal

sudo -u postgres psql -U postgres -c 'SHOW data_directory'
 
# Check cert.
sudo ls /var/lib/postgresql/14/main/server.*

service postgresql restart

cat /var/log/postgresql/postgresql-18-main.log
cat /var/log/postgresql/postgresql-14-main.log

psql -d "dbname=postgres sslmode=require" -h pgsql.elecdove.com -U postgres

DBeaver using SSL: Driver properties -> sslmode: require

Fail2ban​

/etc/fail2ban/filter.d/local-postgresql.conf

[Definition]

failregex = <HOST>\(\d+\) FATAL:  password authentication failed for .+$
            <HOST>\(\d+\) FATAL:  no pg_hba.conf entry for host .+$

ignoreregex = duration:

/etc/fail2ban/jail.d/local-postgresql.conf

[local-postgresql]
enabled  = true
filter   = local-postgresql
action   = iptables[name=PostgreSQL, port=5432, protocol=tcp]
           sendmail-whois[name=PostgreSQL, dest=root@localhost]
logpath  = /var/log/postgresql/postgresql-15-main.log
maxretry = 5

Shell Command​

such command like "createuser" is generate the SQL command and send to PostgreSQL, -e argument will show the generated SQL.

# Create a new user of PostgreSQL.
sudo -u postgres createuser user1

# Create database.
sudo -u postgres createdb database1

PG Console Command​

\conninfo

\dp

# List users
\du

# List table spaces
\db

# List table
\dt

# Use database
\c databbase1;

# Exit PostgreSQL command interface, use Ctrl + d or \q .
\q

# List the database privileges
\l

# List of casts
\dC+
\dCS boolean

SQL Command​

SHOW search_path;

-- Create database
CREATE DATABASE database1;

-- List databases
SELECT datname FROM pg_database;

-- Create user
CREATE USER user1 WITH encrypted PASSWORD 'Passw0rd';

-- List User
SELECT * FROM pg_catalog.pg_user;

-- Modify password
ALTER USER user1 PASSWORD 'postgres_password';

-- Grant CONNECT to the database
GRANT CONNECT ON DATABASE database1 TO user1;

-- Grant all privileges on the database
GRANT ALL ON DATABASE db1 TO user1;
GRANT ALL ON SCHEMA public TO user1;

SELECT * from information_schema.table_privileges WHERE grantee = 'user' LIMIT 5;

-- Returns the name of the current database
SELECT current_database();

-- Remove database
DROP DATABASE database1;

-- Delete Table
DROP TABLE database1;

-- Reset auto increment
ALTER SEQUENCE tick_id_seq RESTART with 1;

-- Remove all tables in current_schema(), replace by your schema name if you're not in current_schema()
DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

-- Remove all sequences
DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT relname FROM pg_class where relkind = 'S') LOOP
        EXECUTE 'DROP SEQUENCE IF EXISTS ' || quote_ident(r.relname) || ' CASCADE';
    END LOOP;
END $$;

Concept​

Migration​

PostgreSQL 15 also revokes the CREATE permission from all users except a database owner from the public (or default) schema.

Each database have standalone pg_catalog.

ODIS​

CREATE TABLE () WITH ( OIDS = FALSE );

OIDs basically give you a built-in id for every row, contained in a system column (as opposed to a user-space column). That's handy for tables where you don't have a primary key, have duplicate rows, etc. For example, if you have a table with two identical rows, and you want to delete the oldest of the two, you could do that using the oid column.

The feature is generally unused in most postgres-backed applications (probably in part because they're non-standard).

If not exists and if exists negative effects​

  • create table if not exists with Flyway
  • drop table if exists
  • create index concurrently if not exists

If our goal is to achieve predictable results, it is best to avoid using if not exists / if exists in database schema migrations.

Trouble Shooting​

DBeaver / PostgreSQL: "Error: database already exists", but I can't find it

Remove database at console.

ERROR: column "xxx" is of type numeric but expression is of type character varying.You will need to rewrite or cast the expression.

url add "?stringtype=unspecified".

Implicit cast integer to boolean

e=no, a=assignment, implicit

Update pg_cast to enable/disable build-in implicit

update pg_cast set castcontext='a' where castsource ='integer'::regtype and casttarget='boolean'::regtype

View pg_catalog schema in DBeaver

Edit Connection => Navigator View is set to Advanced. Under Customize, that Show system objects is enabled.

PgAdmin Utility file not found. Please correct the Binary Path in the Preferences dialog

File→Preferences→Paths→Binary paths→PostgreSQL Binary Path

C:\Program Files\PostgreSQL\15\bin

Install​

Support PostgreSQL 18.x, Ubuntu 24.04

# noble (24.04, LTS)
sudo apt install -y curl
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

. /etc/os-release
sudo sh -c "echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main' > /etc/apt/sources.list.d/pgdg.list"

sudo apt update
sudo apt install postgresql-18

tail /var/log/postgresql/postgresql-18-main.log

Remote Access​

Full control:

sudo -u postgres psql
sudo -u postgres psql -c "SHOW config_file;"

sudo vi /etc/postgresql/18/main/postgresql.conf

listen_addresses = '*'
port = 5432

sudo -u postgres psql -c "SHOW hba_file;"
sudo vi /etc/postgresql/18/main/pg_hba.conf

host    all             all             192.168.0.0/16         md5

systemctl status postgresql

sudo -i -u postgres
psql

CREATE ROLE dbeaver_admin WITH LOGIN PASSWORD 'Passw@rd' SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS;

psql -h localhost -U dbeaver_admin -d postgres

Oracle Cloud​

  1. VCN

Networking -> Virtual cloud networks -> VCN -> Add Ingress Rules

Destination Port Range 5432

  1. IP Table
sudo iptables -L -n -v
sudo apt-get install iptables-persistent
sudo iptables -I INPUT 5 -p tcp --dport 5432 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT

SSL​

Certbot renew
       ↓
Deploy-hook trigger
       ↓
systemctl reload postgresql
       ↓
PostgreSQL use new CA without pause

psql A 10800 132.226.123.125 Disable Proxy

Cloudflare

Create Custom Token Zone DNS Edit Include All zones

sudo apt install certbot letsencrypt python3-certbot-dns-cloudflare
mkdir ~/.secrets
vi ~/.secrets/cloudflare.ini

dns_cloudflare_api_token = <API_TOKEN>

chmod 0700 ~/.secrets/
chmod 0400 ~/.secrets/cloudflare.ini

sudo certbot certonly \
--dns-cloudflare \
--dns-cloudflare-credentials ~/.secrets/cloudflare.ini \
-d domain.com \
--preferred-challenges dns-01 \
--dns-cloudflare-propagation-seconds 60 \
--email [email protected]

FC_CA="/etc/letsencrypt/live/$(ls /etc/letsencrypt/live | grep -v README | head -n 1)/fullchain.pem"
PV_KE="/etc/letsencrypt/live/$(ls /etc/letsencrypt/live | grep -v README | head -n 1)/fullchain.pem"

sudo vi /etc/postgresql/18/main/postgresql.conf

ssl = on
ssl_cert_file = '/etc/letsencrypt/live/db.example.com/fullchain.pem'
ssl_key_file = '/etc/letsencrypt/live/db.example.com/privkey.pem'
ssl_prefer_server_ciphers = on
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'

sudo chown postgres:postgres /etc/letsencrypt/live/db.example.com/privkey.pem
sudo chmod 600 /etc/letsencrypt/live/db.example.com/privkey.pem

sudo chown postgres:postgres $FC_CA
sudo chmod 600 $FC_CA
sudo namei -l /etc/letsencrypt/live/psql.neuhex.com/fullchain.pem f: /etc/letsencrypt/live/psql.neuhex.com/fullchain.pem
sudo -u postgres cat /etc/letsencrypt/live/psql.neuhex.com/fullchain.pem
sudo -u postgres cat "$FC_CA" > /dev/null
sudo chown postgres:postgres /etc/letsencrypt/archive

sudo vi /etc/letsencrypt/renewal-hooks/deploy/reload-postgresql.sh

#!/bin/bash
# When certbot renews the certificate, reload PostgreSQL (no downtime)
/usr/bin/systemctl reload postgresql

sudo chmod +x /etc/letsencrypt/renewal-hooks/deploy/reload-postgresql.sh

sudo certbot renew --dry-run
sudo certbot renew --dry-run -v

# Dry run: skipping deploy hook command: /etc/letsencrypt/renewal-hooks/deploy/reload-postgresql.sh

# DBeaver using SSL: Driver properties -> sslmode: require

SHOW ssl;
SHOW ssl_cert_file;
SHOW ssl_key_file;