# The documentation website of PostgreSQL is www.postgresql.org/docs.
$ sudo addgroup sql
[sudo] password for testuser:
Adding group `sql' (GID 1005) ...
Done.
$ sudo adduser pgsqlown --ingroup sql
$ sudo apt-get update
$ sudo apt-get upgrade
# For the software download, you may choose a more recent version of the database with the following section of this write-up, or skip ahead.
# If you want to install a more recent version:
$ sudo apt-get install curl ca-certificates gnupg
$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 4812 100 4812 0 0 4105 0 0:00:01 0:00:01 --:--:-- 4105
OK
# Create /etc/apt/sources.list.d/pgdg.list with a line for the repository version for your Linux version.
$ lsb_release -c
Codename: bionic
$ cat /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main
$ sudo apt-get update
$ sudo apt-get install postgresql-11
# Use this for the quick install of whatever version is provided at the time of the Ubuntu release. Do one or the other of the prior install or the following install.
$ sudo apt install postgresql postgresql-contrib
# If you are watching /var/log/syslog during the install, you will see entries like:
Apr 16 22:19:16 dell990 systemd[1]: Starting PostgreSQL RDBMS...
Apr 16 22:19:16 dell990 systemd[1]: Started PostgreSQL RDBMS.
Apr 16 22:19:19 dell990 systemd[1]: Reloading.
Apr 16 22:19:19 dell990 systemd[1]: message repeated 2 times: [ Reloading.]
Apr 16 22:19:20 dell990 systemd[1]: Created slice system-postgresql.slice.
Apr 16 22:19:20 dell990 systemd[1]: Starting PostgreSQL Cluster 10-main...
Apr 16 22:19:22 dell990 systemd[1]: Started PostgreSQL Cluster 10-main.
$ ps -ef | grep sql
postgres 6118 1 0 22:19 ? 00:00:00 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf
# You should take a moment to review the config file.
$ more /etc/postgresql/10/main/postgresql.conf
$ grep -v ^\# /etc/postgresql/10/main/postgresql.conf | grep -v ^$ | grep -v $'\t'
# Note the install made the postgres user, with a home directory in /var/lib.
$ tail -1 /etc/passwd
postgres:x:122:123:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
# As configured, the postgres unix account does not allow a direct login, nor "su", because of the "*" (asterisk/star) in the second field of the actual password file.
$ sudo tail -1 /etc/shadow
postgres:*:18369:0:99999:7:::
# The software install made a unix group for the postgres user.
$ tail -1 /etc/group
postgres:x:123:
# All of the running processes for the database owner.
$ ps -fu postgres
UID PID PPID C STIME TTY TIME CMD
postgres 6118 1 0 22:19 ? 00:00:00 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf
postgres 6128 6118 0 22:19 ? 00:00:00 postgres: 10/main: checkpointer process
postgres 6129 6118 0 22:19 ? 00:00:00 postgres: 10/main: writer process
postgres 6130 6118 0 22:19 ? 00:00:00 postgres: 10/main: wal writer process
postgres 6131 6118 0 22:19 ? 00:00:00 postgres: 10/main: autovacuum launcher process
postgres 6133 6118 0 22:19 ? 00:00:00 postgres: 10/main: stats collector process
postgres 6135 6118 0 22:19 ? 00:00:00 postgres: 10/main: bgworker: logical replication launcher
# Note the line with PID 6118. That started the database server and shows the configuration file.
# The software install may have been placed in /usr/share.
$ ls -ld /usr/share post*
drwxr-xr-x 3 root root 4096 Apr 16 22:19 postgresql
drwxr-xr-x 5 root root 4096 Apr 16 22:19 postgresql-common
drwxr-xr-x 253 root root 12288 Apr 16 22:19 /usr/share
# Check if automatic database startup was configured with systemctl. Looks like it was not configured, as there are no new files in /etc/systemd/system.
$ ls -ltr /etc/systemd/system
# Yet there is a systemctl entry.
$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Thu 2020-04-16 22:19:16 MDT; 1 day 1h ago
Main PID: 5093 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 4915)
CGroup: /system.slice/postgresql.service
# It is running from systemctl, so look further for systemctl files.
$ sudo grep -i post /etc/systemd/system/*/* 2>/dev/null
/etc/systemd/system/multi-user.target.wants/postgresql.service:# systemd service for managing all PostgreSQL clusters on the system. This
/etc/systemd/system/multi-user.target.wants/postgresql.service:Description=PostgreSQL RDBMS
# Check if jobs were added in cron. Can we "su" to login to the new account... Is there a password for the user?
$ sudo grep postgres /etc/shadow
postgres:*:18369:0:99999:7:::
# The second field has an asterisk (*), so it is not possible to "su" and enter a password.
# Let's use sudo to become the user and look for a crontab entry.
$ sudo su - postgres
postgres@dell990:~$ id
uid=122(postgres) gid=123(postgres) groups=123(postgres),112(ssl-cert)
postgres@dell990:~$ crontab -l
no crontab for postgres
# We have determined nothing is configured cron, and the database start and stop is configured in systemctl.
# Let's try to log in with the sql interpreter, and then log out.
$ psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.
postgres=# \q
# Exit the sql interpreter with "\q" and press ENTER.
# Confirm which version of the database we are connecting to. Press "q" when you have finished reading the output from the SELECT command.
$ psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.
postgres=# select version();
version
----------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)
(END) version
----------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)
(END)
# Another way to show the database software version while in the sql interpreter.
postgres=# show server_version;
server_version
---------------------------------------
10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)
(1 row)
# We can also ask the postgres executable which version it is.
$ postgres -V
Command 'postgres' not found, did you mean:
command 'postgrey' from deb postgrey
Try: apt install <deb name>
# Confirm we are using the unix login of the software owner, and look at the PATH environment variable.
$ whoami
postgres
$ echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin
# Find where "postgres" was installed in the filesystem.
$ sudo find / -name postgres -print 2>/dev/null
/run/sudo/ts/postgres
/usr/lib/postgresql/10/bin/postgres
# The directory in that second line of output should be added to our PATH shell environment variable. Add just the directory path, not the actual "postgres" command.
$ echo 'export PATH=$PATH:/usr/lib/postgresql/10/bin' >> /var/lib/postgresql/.bashrc
# Login again, or "source" the login file. Type this in the "home" directory.
$ . ./.bashrc
# Check the new setting of PATH shell environment variable.
$ echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin:/usr/lib/postgresql/10/bin/postgres:/usr/lib/postgresql/10/bin
# Try the version command again.
$ postgres -V
postgres (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)
# Log in and find out which role is in use. In this case, it is the same as the unix login.
$ psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
# List table names in this database. In this case, there are none.
postgres=# \d
Did not find any relations.
### Use PostgreSQL perl wrapper to determine what is running. Similar, though different than the earlier "ps" command.
$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
10 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
$ pg_ctlcluster 10 main status
pg_ctl: server is running (PID: 6118)
/usr/lib/postgresql/10/bin/postgres
"-D" "/var/lib/postgresql/10/main" "-c"
"config_file=/etc/postgresql/10/main/postgresql.conf"
# Stop the server. You may want to simultaneously run unix "top" in another window to watch the process.
# While you can use pg_ctlcluster with "stop", you should use the already-configured systemctl.
### Set up a backup. This is a client program which may be run from a different machine.
$ which pg_dumpall
/usr/bin/pg_dumpall
# The command to backup all databases should run quickly because nothing has been added yet.
$ pg_dumpall > /tmp/postgres.backup
# The curious may want to look at the backup file.
$ file /tmp/postgres.backup
/tmp/postgres.backup: ASCII text
$ more /tmp/postgres.backup
--
-- PostgreSQL database cluster dump
--
... and the backup file continues and ends with ...
--
-- PostgreSQL database cluster dump complete
--
# Add the following line in unix user postgres crontab.
$ crontab -l
* 1 * * * /usr/lib/postgresql/10/bin/pg_dumpall > /tmp/postgres.backup.$(/bin/date +%Y%m%d.%H%M%S) 1>>/tmp/postgres.cron 2>>&1
No comments:
Post a Comment