PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 4812 100 4812 0 0 5453 0 --:--:-- --:--:-- --:--:-- 5449
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /data/postgresdb17 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/lib/postgresql/17/bin/pg_ctl -D /data/postgresdb/17 -l logfile start
Edit the PostgreSQL data directory on configuration, to redirect the store to the new location
Replace the line 42 with /var/lib/postgresql/17/main to the next. Save and exit
data_directory = '/data/postgresdb/17'
Restart PostgreSQL to apply changes and monitor the correct status of the main instance and sub-instance monitoring sessions before
sudosystemctlrestartpostgresql
You can monitor the PostgreSQL main instance by the systemd journal and check the log output. You can exit the monitoring at any time with Ctrl-C
journalctl-fupostgresql
Expected output:
Nov 08 11:51:10 ramix systemd[1]: Stopped PostgreSQL RDBMS.
Nov 08 11:51:10 ramix systemd[1]: Stopping PostgreSQL RDBMS...
Nov 08 11:51:13 ramix systemd[1]: Starting PostgreSQL RDBMS...
Nov 08 11:51:13 ramix systemd[1]: Finished PostgreSQL RDBMS.
You can monitor the PostgreSQL sub-instance by the systemd journal and check log output. You can exit monitoring at any time with Ctrl-C
journalctl-fupostgresql@17-main
Example of the expected output:
Nov 08 11:51:10 ramix systemd[1]: Stopping PostgreSQL Cluster 17-main...
Nov 08 11:51:11 ramix systemd[1]: [email protected]: Succeeded.
Nov 08 11:51:11 ramix systemd[1]: Stopped PostgreSQL Cluster 17-main.
Nov 08 11:51:11 ramix systemd[1]: [email protected]: Consumed 1h 10min 8.677s CPU time.
Nov 08 11:51:11 ramix systemd[1]: Starting PostgreSQL Cluster 17-main...
Nov 08 11:51:13 ramix systemd[1]: Started PostgreSQL Cluster 17-main.
You can check if the cluster is on status "online" by
pg_lsclusters
Expected output:
Ver Cluster Port Status Owner Data directory Log file
17 main 5432 online postgres /data/postgresdb/17 /var/log/postgresql/postgresql-17-main.log
(Optional) -> If you want, you can disable the autoboot option for PostgreSQL (not recommended) using:
sudosystemctldisablepostgresql
Expected output:
Synchronizing state of postgresql.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install disable postgresql
Removed /etc/systemd/system/multi-user.target.wants/postgresql.service.
Ensure PostgreSQL is listening on the default relational database port
Create a new database admin user and assign the password "admin" with the automatically created user for the PostgreSQL installation, called postgres
sudo-upostgrespsql-c"CREATE ROLE admin WITH LOGIN CREATEDB PASSWORD 'admin';"
Expected output:
CREATE ROLE
Congrats! You have PostgreSQL ready to use as a database backend by another software
Extras (optional)
Some useful PostgreSQL commands
With user admin, enter the PostgreSQL CLI with the user postgres. The prompt should change to postgres=#
sudo-upostgrespsql
Example of expected output:
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1))
Type "help" for help.
postgres=#
Type \q command and enter to exit PostgreSQL CLI and exit to come back to the admin user
List the global existing users and roles associated
Type the next command and enter
\du
Example of expected output:
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
admin | Create DB
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS