In this section you will learn about different options to install, configure and check your PostgreSQL database. If you already know how to deploy a PostgreSQL database, you can skip this learning path, and instead explore the Learn how to Tune PostgreSQL learning path.
There are numerous ways to deploy PostgreSQL on Arm. Bare metal, cloud VMs, or the various SQL services that cloud providers offer. If you already have an Arm system, you can skip over this subsection and continue reading.
PostgreSQL is a large project with many features. It is recommended that the PostgreSQL documentation be explored.
If you are using a cloud service like AWS RDS, then the installation of PostgreSQL is handled by those services. However, if you are working with a bare metal or cloud node, there are a few different installation options . You should decide what approach you want to take for installing PostgreSQL after reviewing the documentation.
Getting PostgreSQL up and running is easy. This is because the default out of box configuration will work. However, this default configuration is most likely under optimized. A graph of the performance difference between an out of box PostgreSQL database and a tuned database is shown in the Learn how to Tune PostgreSQL learning path. For the purpose of learning, it’s ok to start with the default configuration. Once you have that working, you should read the PostgreSQL server configuration documentation , and follow the Learn how to Tune PostgreSQL learning path.
It can be helpful to know the build configuration of an installation of PostgreSQL. Run pg_config to get this information. Shown below is an example output:
BINDIR = /usr/lib/postgresql/14/bin
DOCDIR = /usr/share/doc/postgresql-doc-14
HTMLDIR = /usr/share/doc/postgresql-doc-14
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/14/server
LIBDIR = /usr/lib/aarch64-linux-gnu
PKGLIBDIR = /usr/lib/postgresql/14/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/14/man
SHAREDIR = /usr/share/postgresql/14
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/14/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--build=aarch64-linux-gnu' '--prefix=/usr' '--includedir=${prefix}/include' '--mandir=${prefix}/share/man' '--infodir=${prefix}/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--disable-option-checking' '--disable-silent-rules' '--libdir=${prefix}/lib/aarch64-linux-gnu' '--runstatedir=/run' '--disable-maintainer-mode' '--disable-dependency-tracking' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--mandir=/usr/share/postgresql/14/man' '--docdir=/usr/share/doc/postgresql-doc-14' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/14' '--bindir=/usr/lib/postgresql/14/bin' '--libdir=/usr/lib/aarch64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--with-extra-version= (Ubuntu 14.8-0ubuntu0.22.04.1)' '--enable-nls' '--enable-thread-safety' '--enable-debug' '--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-gssapi' '--with-ldap' '--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 'AWK=mawk' 'MKDIR_P=/bin/mkdir -p' 'PROVE=/usr/bin/prove' 'PYTHON=/usr/bin/python3' 'TAR=/bin/tar' 'XSLTPROC=xsltproc --nonet' 'CFLAGS=-g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security' 'LDFLAGS=-Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects -flto=auto -Wl,-z,relro -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-14' 'CLANG=/usr/bin/clang-14' '--with-lz4' '--with-systemd' '--with-selinux' 'build_alias=aarch64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security'
CC = gcc
CPPFLAGS = -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -moutline-atomics -g -g -O2 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects -flto=auto -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-14/lib -Wl,--as-needed
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lselinux -llz4 -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm
VERSION = PostgreSQL 14.8
The switches CFLAGS, CXXFLAGS, and LDFLAGS listed in the CONFIGURE line can be very helpful if you plan to build PostgreSQL from source. Tuning compilation options is discussed in the
Learn how to Tune PostgreSQL
learning path.
Installations of PostgreSQL will also install a CLI client application called
psql
. Once a database is up and running, this tool can be used to connect to the database and make sure it is working. Review the
instructions
on how to use the psql CLI tool.
Below is sample output of what you should see when you connect to the database successfully.
psql (15.2)
Type "help" for help.
postgres=#
Create a new database using the command shown:
postgres=# create database testdb;
CREATE DATABASE
List all databases:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
(4 rows)
Switch to the new databases:
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".
Create a new table in the database:
postgres=# CREATE TABLE company ( emp_name VARCHAR, emp_dpt VARCHAR);
CREATE TABLE
Display the database tables:
postgres=# \dt;
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | company | table | postgres
(1 row)
Insert data into the table:
postgres=# INSERT INTO company VALUES ('Herry', 'Development'), ('Tom', 'Testing'),('Ankit', 'Sales'),('Manoj', 'HR'),('Noy', 'Presales');
INSERT 0 5
Print the contents of the table:
postgres=# select * from company;
emp_name | emp_dpt
----------+-------------
Herry | Development
Tom | Testing
Ankit | Sales
Manoj | HR
Noy | Presales
(5 rows)
Using these sample commands shown above you have successfully validated your installation of PostgreSQL database.