Starting Postgresql Instance
If installed from pre-built packages (yum/apt install postgresql…), services are
created according to the operating system. Thus, it can be stopped and started with
systemctl commands and its startup can be automated (systemctl enable
postgres).
Note: In the appendix chapters, where the installation and architecture from the prebuilt
package were explained, and in the first chapter, you can examine the installation
example from the source code. (to access the installation from the source kod –> )
| Cluster (data directory) | /pg/data/13.6 |
| Binary location | /pg/home/13.6/bin |
| Port (changed due to other instance) | 5434 |
# The following lines can be added to the bottom of the .profile file in order repeat the full path every time. vim /var/lib/postgresql/.profile export PGDATA=/pg/data/13.6 export PGDATABASE=postgres export PGUSER=postgres export PGPORT=5434 export PATH=/pg/home/13.6/bin:$PATH # In order to enable the current variables in the current session. source ~/.profile # To check. postgres@srv1:~$ which postgres /pg/home/13.6/bin/postgres
The following commands are executed to start the database.
postgres@srv1:~$ postgres -D /pg/data/13.6/ LOG:starting PostgreSQL 13.6 on x86_64-pc-linux-gnu,compiled by gcc (Debian 8.3.0-6)8.3.0,64-bit [9860] LOG: listening on IPv4 address “0.0.0.0”, port 5434 [9860] LOG: listening on IPv6 address “::”, port 5434 [9860] LOG: listening on Unix socket “/tmp/.s.PGSQL.5434” [9861] LOG: database system was shut down at 16:13:34 +03 [9860] LOG: database system is ready to accept connections
Note: In this way, the postgresql instance runs in the foreground. If the SSH session is
terminated, the database is also closed.
If PGDATA is defined in environment variables, “–D” parameter is not required.
In this manner, stdout and sterr will be directed to the log file by running in the background. postgres -D /pg/data/13.6/ > postgresql.log 2>&1 & # To run in debug mode (-d 5), should be used with caution as log file grows very quickly. postgres -D /pg/data/13.6/ -d 5 > debugpostgres.log 2>&1 &
postgres : It can take many parameters, there are details on man postgres or postgresql.org.
pg_ctl : It works similarly to postgres described above, but pg_ctl is more useful.
# Runs in the background and redirects the log to the postgresql.log file. postgres@srv1:~$ pg_ctl start -l postgresql.log # -D option can be used here as well. pg_ctl -D /pg/data/13.6/ -l postgresql.log 2>&1 & # Status, stop and restart options are also available. postgres@srv1:~$ pg_ctl status/stop/restart -l postgresql.log # Reload reads the configuration file and enables the settings that do not require a restart. postgres@srv1:~$ pg_ctl reload -D /pg/data/13.6 # ‘promote’ option switches the hot standby server to primary, that is, it opens it for writing and reading. postgres@srv1:~$ pg_ctl promote -D /pg/data/13.6
# With root privilege, systemctl can be started and stopped. sudo systemctl start/stop/restart postgresql@14-main.service # To check status sudo systemctl status postgresql@14-main.service # With enable option, it automatically starts and stops postgres on startup and shutdown. sudo systemctl enable postgresql@14-main.service
systemctl :Startup and shutdown scripts can be integrated with the operating system’s
systemd service management tool. The database can be automatically stopped and
started when the operating system is shut down or booted. Systemd services come
automatically when installed from in-build packages.
pg_ctlcluster : Simplifies systemd integration. In multi-cluster environments, it calls the
cluster directory of the database to be processed and the correct pg_ctl.
Checking Instance
pgrep -u postgres -fa -- -D 5932 /pg/home/13.6/bin/postgres -D /pg/data/13.6 --config-file=/pg/data/13.6/postgresql.conf pgrep -fa -- -D |grep postgres 5932 /pg/home/13.6/bin/postgres -D /pg/data/13.6 –config file=/pg/data/13.6/postgresql.conf # Port information sed -n 4p $PGDATA/postmaster.pid 5434 # or sed
With pgrep and sed tools, it is possible to get information such as the process
number (PID) and TCP port of the config file where the instance is running.
Stopping Postgresql Instance
postgres@srv1:~$ pg_ctl -D /pg/data/13.6 stop -m s waiting for server to shut down....................... done postgres@srv1:~$ pg_ctl -D /pg/data/13.6 stop -m f waiting for server to shut down.... done server stopped postgres@srv1:~$ pg_ctl -D /pg/data/13.6 stop -m i waiting for server to shut down.... done server stopped
Smart Mode: (SIGTERM) New sessions (connections) are not allowed. It doesn’t
terminate existing sessions, it will let it run. However, it stops the instance when all
sessions are terminated. In a production environment database, you may have to wait
long time (maybe months) for all sessions to expire.
Fast Mode: (SIGINT) New sessions (connections) are not allowed. Terminates current
sessions. if it is committed, all sessions are terminated and if the user does not commit,
it rollbacks. Stops fast and consistent. No recovery is required at startup. It is the
default shutdown mode.
Immediate Mode: Immediately terminates all server processes. It’s the same as
unplugging the server’s power.
The changed buffer area is closed inconsistently because the datafiles cannot be
written and the uncommitted changes cannot be rolled back.
Requires recovery from transaction (WAL – write-ahead-log) log files at startup.