preface
Recently, a classmate asked how to compile and install PostgreSQL under Ubuntu. This time, let's talk about how to install PostgreSQL database in Ubuntu operating system. So, installing PostgreSQL is the easiest thing for DBA s. We know that PostgreSQL has three installation methods in RedHat family system, namely source code installation, rpm installation and yum installation. Of course, rpm installation and yum installation can be regarded as similar installations. In addition, it is to use the compiled binary installation. For the Ubuntu operating system, except that the one click apt get install installation is different from the red hat family installation, the source code installation and binary installation are the same. However, if you use the source installation, you need to solve some dependencies. This article is to install PostgreSQL on the latest version of Ubuntu server operating system.
NOTE: the latest PostgreSQL installation package, version 12.9, has been integrated into the current Ubuntu operating system
1, Install using apt get
Apt get command is the command of Ubuntu system management software, which can be used to manage, remove, empty and check the software installed on Ubuntu.
1. Operating system version
The operating system is Ubuntu 20.04 3 server operating system
root@developer:~# lsb_release -a No LSB modules are available. Distributor ID:Ubuntu Description:Ubuntu 20.04.3 LTS Release:20.04 Codename:focal
2. Check the PostgreSQL package available on Ubuntu
You can list PostgreSQL related software packages through apt list
root@developer:~# apt list | grep -w postgresql-12 | tail -1 WARNING: apt does not have a stable CLI interface. Use with caution in scripts. postgresql-12/focal-updates,focal-security 12.9-0ubuntu0.20.04.1 amd64
3. Perform installation
postgresql-12/focal-updates,focal-security 12.9-0ubuntu0.20.04.1 amd64
root@developer:~# apt-get install postgresql-12 -y
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
libpython2.7 libpython2.7-dev libpython2.7-minimal libpython2.7-stdlib
Use 'apt autoremove' to remove them.
The following NEW packages will be installed:
postgresql-12
0 upgraded, 1 newly installed, 0 to remove and 10 not upgraded.
Need to get 0 B/13.5 MB of archives.
After this operation, 41.1 MB of additional disk space will be used.
Preconfiguring packages ...
Selecting previously unselected package postgresql-12.
(Reading database ... 158185 files and directories currently installed.)
Preparing to unpack .../postgresql-12_12.9-0ubuntu0.20.04.1_amd64.deb ...
Unpacking postgresql-12 (12.9-0ubuntu0.20.04.1) ...
Setting up postgresql-12 (12.9-0ubuntu0.20.04.1) ...
Creating new PostgreSQL cluster 12/main ...
/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5
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 /var/lib/postgresql/12/main ... 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
Success. You can now start the database server using:
pg_ctlcluster 12 main start
Ver Cluster Port Status Owner Data directory Log file
12 main 5432 down postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
update-alternatives: using /usr/share/postgresql/12/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Processing triggers for postgresql-common (214ubuntu0.1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
en_us
Removing obsolete dictionary files:4. Start the PostgreSQL database cluster according to the above output
root@developer:~# pg_ctlcluster 12 main start
It should be noted that the PostgreSQL database version included in the Ubuntu version encapsulates the database cluster management command as pg_ctlcluster command. Therefore, the command can execute and pg_ctl similar actions, such as start, stop, restart, load, etc.
5. Inspection process
root@developer:~# ps -ef |grep postgres postgres 69578 1 0 07:54 ? 00:00:00 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf postgres 69580 69578 0 07:54 ? 00:00:00 postgres: 12/main: checkpointer postgres 69581 69578 0 07:54 ? 00:00:00 postgres: 12/main: background writer postgres 69582 69578 0 07:54 ? 00:00:00 postgres: 12/main: walwriter postgres 69583 69578 0 07:54 ? 00:00:00 postgres: 12/main: autovacuum launcher postgres 69584 69578 0 07:54 ? 00:00:00 postgres: 12/main: stats collector postgres 69585 69578 0 07:54 ? 00:00:00 postgres: 12/main: logical replication launcher
6. Check the port
root@developer:~# netstat -anlp | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 69578/postgres unix 2 [ ACC ] STREAM LISTENING 246072 69578/postgres /var/run/postgresql/.s.PGSQL.5432
7. Log in to the database
root@developer:~# psql -U postgres -d postgres -p 5432 psql: error: FATAL: Peer authentication failed for user "postgres"
The peer authentication error is reported when logging into the database, so we know that the connection authentication is based on pg_hba.conf entry, so the error message needs to be modified pg_hba.conf configuration file.
To modify the configuration file, you need to know which directory to distribute the files using apt get. In the Ubuntu system, you can find where the software is installed through whereis, as shown below, where the PostgreSQL software is installed
root@developer:~# whereis -u postgresql postgresql: /usr/lib/postgresql /etc/postgresql /usr/share/postgresql
As can be seen from the above output, the software is installed in the above three directories. Of course, the executable program is installed in the / usr/bin directory, as shown below
root@developer:~# cd /usr/bin/ root@developer:/usr/bin# ls -l pg* lrwxrwxrwx 1 root root 37 Aug 24 2020 pg_archivecleanup -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root 37 Aug 24 2020 pg_basebackup -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root 37 Aug 24 2020 pgbench -> ../share/postgresql-common/pg_wrapper -rwxr-xr-x 1 root root 9707 Aug 24 2020 pg_buildext -rwxr-xr-x 1 root root 1229 Aug 24 2020 pg_config -rwxr-xr-x 1 root root 6262 Aug 24 2020 pg_conftool -rwxr-xr-x 1 root root 34684 Aug 24 2020 pg_createcluster -rwxr-xr-x 1 root root 23919 Aug 24 2020 pg_ctlcluster -rwxr-xr-x 1 root root 7603 Aug 24 2020 pg_dropcluster lrwxrwxrwx 1 root root 37 Aug 24 2020 pg_dump -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root 37 Aug 24 2020 pg_dumpall -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root 37 Aug 24 2020 pg_isready -> ../share/postgresql-common/pg_wrapper -rwxr-xr-x 1 root root 5268 Aug 24 2020 pg_lsclusters lrwxrwxrwx 1 root root 37 Aug 24 2020 pg_receivewal -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root 37 Aug 24 2020 pg_receivexlog -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root 37 Aug 24 2020 pg_recvlogical -> ../share/postgresql-common/pg_wrapper -rwxr-xr-x 1 root root 5887 Aug 24 2020 pg_renamecluster -rwxr-xr-x 1 root root 30968 Sep 9 12:59 pgrep lrwxrwxrwx 1 root root 37 Aug 24 2020 pg_restore -> ../share/postgresql-common/pg_wrapper -rwxr-xr-x 1 root root 33434 Aug 24 2020 pg_upgradecluster -rwxr-xr-x 1 root root 7859 Aug 24 2020 pg_virtualenv
Modify PG_ hba. The conf file passes the unix socket authentication, and the method is trust
root@developer:~# cd /etc/postgresql/12/main/ root@developer:/etc/postgresql/12/main# vi pg_hba.conf root@developer:/etc/postgresql/12/main# cat pg_hba.conf | grep trust # METHOD can be "trust", "reject", "md5", "password", "scram-sha-256", local all postgres trust
Try connecting again. Before connecting, you need to reload the host based configuration file
root@developer:~# pg_ctlcluster reload 12 main root@developer:~# psql -U postgres -d postgres -p 5432 psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1)) Type "help" for help. postgres=#
You can see that the database version is version 12.9.
8. Use system commands to manage database instances
postgresql. The service system daemon file, like the redhat family system, is still stored in the / usr/lib/systemd/system directory
root@developer:/usr/lib/systemd/system# pwd /usr/lib/systemd/system root@developer:/usr/lib/systemd/system# ls postgresql.service postgresql.service
The PostgreSQL database installed using apt get can also be managed and maintained using the systemctl command
2, Compile and install using source code
1. Upload the installation package to / opt/software directory and unzip it
root@developer:~# cd /opt/software/ root@developer:/opt/software# ls postgresql-13.5.tar.bz2 root@developer:/opt/software# tar -jxf postgresql-13.5.tar.bz2 root@developer:/opt/software# ls postgresql-13.5 postgresql-13.5.tar.bz2
2. Install the necessary dependencies
apt-get install -y systemtap-sdt-dev apt-get install -y libicu-dev apt-get install -y libreadline-dev apt-get install -y zlib1g-dev apt-get install -y libssl-dev apt-get install -y libpam-dev apt-get install -y libxml2-dev apt-get install -y libxslt-dev apt-get install -y libldap-dev apt-get install -y libsystemd-dev apt-get install -y getext apt-get install -y tcl-dev apt-get install -y libpython3-dev apt-get install -y libperl-dev
3. Switch to the database software decompression directory and execute the pre compilation configuration
Switch directory
root@developer:~# cd /opt/software/postgresql-13.5/ root@developer:/opt/software/postgresql-13.5#
Perform pre compile checks
export PREFIX="/usr/local/pg13"
export PGPORT=10000
./configure \
--prefix=${PREFIX} \
--exec-prefix=${PREFIX}/pgsql \
--bindir=${PREFIX}/pgsql/bin \
--sysconfdir=${PREFIX}/etc \
--libdir=${PREFIX}/pgsql/lib \
--includedir=${PREFIX}/include \
--datarootdir=${PREFIX}/share \
--localedir=${PREFIX}/locale \
--mandir=${PREFIX}/locale/man \
--docdir=${PREFIX}/locale/doc \
--htmldir=${PREFIX}/locale/html \
--enable-nls='en_US zh_CN' \
--with-perl \
--with-python \
--with-tcl \
--with-icu \
--with-openssl \
--with-ldap \
--with-pam \
--with-systemd \
--with-libxml \
--with-libxslt \
--with-readline \
--with-zlib \
--with-pgport=${PGPORT}4. Execute the compile and install commands
root@developer:/opt/software/postgresql-13.5# make world -j8 && make install-world -j8
The location of compilation and installation is as follows:
root@developer:/usr/local/pg13# pwd /usr/local/pg13 root@developer:/usr/local/pg13# ls include locale pgsql share
5. Create operating system user and database cluster storage directory
Create postgres user
root@developer:~# useradd -u 2000 -c "PostgreSQL db user" -b /home -m -k /etc/skel -s /bin/bash postgres root@developer:~# passwd postgres New password: Retype new password: passwd: password updated successfully
Create database cluster storage directory / data/pg13/pgdata
root@developer:~# mkdir -p /data/pg13/pgdata
to grant authorization
root@developer:~# chown postgres.postgres -R /data
6. Switch to the postgres user configuration environment variable
root@developer:~# su - postgres
postgres@developer:~$ vi .bashrc
postgres@developer:~$ tail -4 .bashrc
export PGHOME=/usr/local/pg13
export PGDATA=/data/pg13/pgdata
export LD_LIBRARY_PATH=${PGHOME}/pgsql/lib:${LD_LIBRARY_PATH}
export PATH=${PGHOME}/pgsql/bin:${PATH}
postgres@developer:~$ . .bashrc7. Initialize database cluster
postgres@developer:~$ initdb -D $PGDATA -k
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 enabled.
fixing permissions on existing directory /data/pg13/pgdata ... 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
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:
pg_ctl -D /data/pg13/pgdata -l logfile start8. Start the database
postgres@developer:~$ pg_ctl start -D $PGDATA -l /tmp/logfile waiting for server to start.... done server started
9. Log in to the database
postgres@developer:~$ psql -d postgres
psql (13.5)
Type "help" for help.
postgres=# select version();
version
------------------------------------------------------------------------------------------------------
PostgreSQL 13.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)summary
The above two methods are the installation of PostgreSQL under Ubuntu operating system. It should be noted that in Ubuntu system, PostgreSQL installed using apt get is based on the secondary packaging software of PostgreSQL. Therefore, there will be differences between command and source code compilation and installation. Such as cluster startup, database parameter modification, database deletion and creation, user creation and deletion, etc.