Show Menu
Cheatography
  1. Home >
  2. Programming >

PostgreSQL Installation on Ubuntu Cheat Sheet by

PostgreSQL Installation (Ubuntu)

Docume­ntation

Preparing for the Instal­lation

If PostgreSQL 15 package is not available in the default package reposi­tory, enable its official package repository
Execute update
$ sudo apt update && sudo apt upgrade

Check PostgreSQL Version

$postgres --version or $postgres -V
If not found execute $locate bin/po­stgres
and then $/usr/­lib­/po­stg­res­ql/­15/­bin­/po­stgres -V
Check Postgres version from SQL Shell
$sudo -u postgres psql
# SELECT version();

Instal­lation

Ubuntu
install $ sudo apt install -y postgr­esql-15
uninstall $ sudo apt-get --purge remove postgresql postgr­esql-*
Server config
sudo /usr/l­ib/­pos­tgr­esq­l/1­5/b­in/­pg_­config --conf­igure
List clusters
$ sudo pg_lsc­lusters
Creat and drop a cluster
$ sudo pg_cre­ate­cluster [options] version name
$ sudo pg_dro­pcl­uster [--stop] version name
Start, stop, restart service and cluster
When installing from a package, the launch of the PostgreSQL cluster is added to the OS startup settings. Therefore, after loading the operating system, you do not need to start PostgreSQL separa­tely.
Managing the PostgreSQL service
$ sudo systemctl start | stop | restart postgresql
You can explicitly control the cluster with the following commands:
$ sudo pg_ctl­cluster 15 main start | stop | restart | status | reload

Directory

Instal­lation directory
/usr/l­­ib­/­p­os­­tgr­­es­ql/15
Config­uration directory
/etc/p­ost­gre­sql­/15­/main
Default cluster location main (PGDATA)
/var/l­­ib­/­p­os­­tgr­­es­q­l­/1­­5/main
Database direct­ories
/var/l­ib/­pos­tgr­esq­l/1­5/m­ain­/base
# select oid, datname from pg_dat­abase;
Data location
# SHOW data_d­­ir­e­c­tory;
/var/l­ib/­pos­tgr­esq­l/1­5/main
Server message log
$ ls -l /var/l­­og­/­p­os­­tgr­­es­q­l­/p­­ost­­gr­e­s­ql­­-15­­-m­a­i­n.log
$ tail -n 10 /var/l­­og­/­p­os­­tgr­­es­q­l­/p­­ost­­gr­e­s­ql­­-15­­-m­a­i­n.log
Links

User creation

Set a password for the postgres user
$sudo -u postgres psql
# \password postgres
Enter new password:
or
# ALTER USER postgres PASSWORD '<n­ew-­pas­swo­rd>';
user list
# \du
Creating a new role (user)
# create user <us­­er­n­a­me> with password '123456';
# alter user <us­ern­ame> with SUPERUSER;
or
$sudo -u postgres createuser --inte­ractive
 

Tablespace

List tables­paces
# SELECT * FROM pg_tab­les­pace; or # \db
CREATE TABLESPACE
$ sudo mkdir /var/l­ib/­pos­tgr­esq­l/t­est_dir
$ sudo chown postgres /var/l­ib/­pos­tgr­esq­l/t­est_dir
# CREATE TABLESPACE test LOCATION '/var/­lib­/po­stg­res­ql/­tes­t_dir';
Creating a database in a new tablespace
# CREATE DATABASE appdb TABLESPACE test;
Tablespace size
SELECT pg_siz­e_p­retty( pg_tab­les­pac­e_s­ize­('t­est') );
Delet tablespace
# DROP TABLESPACE test;
Moving a directory with default data
Stop cluster
sudo pg_ctl­­cl­uster 15 main stop
Copy data to new directory
Change the data_d­ire­ctory variable in the config file
/etc/p­ost­gre­sql­/15­/ma­in/­pos­tgr­esq­l.conf
Start cluster
sudo pg_ctl­­cl­uster 15 main start
Links

Connection config­uration

Сonfig­uration file
/etc/p­ost­gre­sql­/15­/ma­in/­pos­tgr­esq­l.conf
edit the listen­_ad­dresses from localhost to *. Enable the listen­_ad­dresses by removing the #
/etc/p­ost­gre­sql­/15­/ma­in/­pg_­hba.conf
To connect to a particular database, a user must not only pass the pg_hba.conf checks, but must have the CONNECT privilege for the database.
GRANT CONNECT ON DATABASE databa­se_name TO username;
A record can have several formats:
local database user auth-m­ethod [auth-­opt­ions]
host database user address auth-m­ethod [auth-­opt­ions]
host database user IP-address IP-mask auth-m­ethod [auth-­opt­ions]
Allow any user from host 192.16­8.12.10 to connect to database "­pos­tgr­es" if the user's password is correctly supplied.
# TYPE DATABASE USER ADDRESS METHOD
host postgres all 192.16­8.1­2.10/32 scram-­sha-256
from all IP
host postgres all 0.0.0.0/0 scram-­sha-256

Backup & Restore

pg_dumpall
pg_dumpall — extract a PostgreSQL database cluster into a script file
pg_dumpall [conne­cti­on-­opt­ion...] [optio­n...]
$ pg_dumpall > db.out
$ psql -f db.out postgres
pg_dump
pg_dump — extract a PostgreSQL database into a script file or other archive file
pg_dump [conne­cti­on-­opt­ion...] [optio­n...] [dbname]
To dump a database called mydb into an SQL-script file:
$ pg_dump mydb > db.sql
To reload such a script into a (freshly created) database named newdb:
$ psql -d newdb -f db.sql
To dump a database into a custom­-format archive file:
$ pg_dump -Fc mydb > db.dump
To dump a database into a direct­ory­-format archive:
$ pg_dump -Fd mydb -f dumpdir
To reload an archive file into a (freshly created) database named newdb:
$ pg_restore -d newdb db.dump
To reload an archive file into the same database it was dumped from, discarding the current contents of that database:
$ pg_restore -d postgres --clean --create db.dump
Links
           
 

Metadata

  • Languages:
  • Published: 4th April, 2023

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          PostgreSQL Cheat Sheet
          SQL Cheat Sheet