PostgreSQL Installation (Ubuntu)
|
If PostgreSQL 15 package is not available in the default package repository, enable its official package repository
|
|
Execute update
|
|
$ sudo apt update && sudo apt upgrade
|
|
$postgres --version or $postgres -V
|
|
If not found execute $locate bin/postgres
and then $/usr/lib/postgresql/15/bin/postgres -V |
|
Check Postgres version from SQL Shell
$sudo -u postgres psql # SELECT version(); |
|
Ubuntu
|
|
install $ sudo apt install -y postgresql-15
uninstall $ sudo apt-get --purge remove postgresql postgresql-* |
|
Server config
|
|
sudo /usr/lib/postgresql/15/bin/pg_config --configure
|
|
List clusters
$ sudo pg_lsclusters |
|
Creat and drop a cluster
|
|
$ sudo pg_createcluster [options] version name
$ sudo pg_dropcluster [--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 separately.
|
|
Managing the PostgreSQL service
|
|
$ sudo systemctl start | stop | restart postgresql
|
|
You can explicitly control the cluster with the following commands:
$ sudo pg_ctlcluster 15 main start | stop | restart | status | reload |
|
Installation directory
|
|
/usr/lib/postgresql/15
|
|
Configuration directory
|
|
/etc/postgresql/15/main
|
|
Default cluster location main (PGDATA)
|
|
/var/lib/postgresql/15/main
|
|
Database directories
|
|
/var/lib/postgresql/15/main/base
# select oid, datname from pg_database; |
|
Data location
|
|
# SHOW data_directory;
/var/lib/postgresql/15/main |
|
Server message log
|
|
$ ls -l /var/log/postgresql/postgresql-15-main.log
|
|
$ tail -n 10 /var/log/postgresql/postgresql-15-main.log
|
|
Links
|
|
Set a password for the postgres user
|
|
$sudo -u postgres psql
# \password postgres Enter new password: or # ALTER USER postgres PASSWORD '<new-password>'; |
|
user list
|
|
# \du
|
|
Creating a new role (user)
|
|
# create user <username> with password '123456';
# alter user <username> with SUPERUSER; or $sudo -u postgres createuser --interactive |
|
List tablespaces
|
|
# SELECT * FROM pg_tablespace; or # \db
|
|
CREATE TABLESPACE
|
|
$ sudo mkdir /var/lib/postgresql/test_dir
$ sudo chown postgres /var/lib/postgresql/test_dir # CREATE TABLESPACE test LOCATION '/var/lib/postgresql/test_dir'; |
|
Creating a database in a new tablespace
|
|
# CREATE DATABASE appdb TABLESPACE test;
|
|
Tablespace size
|
|
SELECT pg_size_pretty( pg_tablespace_size('test') );
|
|
Delet tablespace
|
|
# DROP TABLESPACE test;
|
|
Moving a directory with default data
|
|
Stop cluster
sudo pg_ctlcluster 15 main stop Copy data to new directory Change the data_directory variable in the config file /etc/postgresql/15/main/postgresql.conf Start cluster sudo pg_ctlcluster 15 main start |
|
Links
|
|
Сonfiguration file
|
|
/etc/postgresql/15/main/postgresql.conf
edit the listen_addresses from localhost to *. Enable the listen_addresses by removing the # |
|
/etc/postgresql/15/main/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 database_name TO username; |
|
A record can have several formats:
|
|
local database user auth-method [auth-options]
|
|
host database user address auth-method [auth-options]
|
|
host database user IP-address IP-mask auth-method [auth-options]
|
|
Allow any user from host 192.168.12.10 to connect to database "postgres" if the user's password is correctly supplied.
|
|
# TYPE DATABASE USER ADDRESS METHOD
host postgres all 192.168.12.10/32 scram-sha-256 |
|
from all IP
host postgres all 0.0.0.0/0 scram-sha-256 |
|
pg_dumpall
|
|
pg_dumpall — extract a PostgreSQL database cluster into a script file
|
|
pg_dumpall [connection-option...] [option...]
|
|
$ 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 [connection-option...] [option...] [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 directory-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
|
No comments yet.
Cheatography n. The art of mapping complex subjects into clear, instantly navigable reference guides.
