Configuring PostgreSQL

Install PostgreSQL

PageSeeder v5.99 or higher supports PostgreSQL v12 to v14 which can be downloaded from https://www.postgresql.org  for Windows or installed on Linux using commands like:

# yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# dnf -qy module disable postgresql
# dnf -y install postgresql12 postgresql12-server
# /usr/pgsql-12/bin/postgresql-12-setup initdb
# systemctl enable postgresql-12
# systemctl start postgresql-12

Set authentication to md5

On Linux, so PageSeeder can login to PostgreSQL using passwords, edit the /var/lib/pgsql/12/data/pg_hba.conf file and change the line host  all  all  127.0.0.1/32  ident as follows, then enter systemctl restart postgresql-12

host    all       all       127.0.0.1/32     scram-sha-256

Set the postgres password

On Linux, so PageSeeder can set up its own user and database, you need to set a password for the login name postgres as follows. Later, in the PageSeeder setup screen, you must enter these details under Database administrator: login name and password.

$ sudo -u postgres psql
# \password
Enter new password:
Enter it again:
# \q

Changing where data is stored

On Linux PostgreSQL usually stores data under /var/lib/pgsql/12/data, but if you want to use mounted storage for pageseeder (for example storage mapped to the /opt folder), you can configure PostgreSQL to store data there as well. To do this, follow these steps:

Stop PostgreSQL by entering:

# systemctl stop postgresql-12

Move the existing PostgreSQL data to the new location:

# mv /var/lib/pgsql/12/data> /opt/pgsql/data

Edit the service by entering systemctl edit postgresql-12, pressing Insert , entering the following, pressing Esc and then :x to save (this creates a file /etc/systemd/system/postgresql-12.service.d/override.conf ).

[Service]
Environment=PGDATA=/opt/pgsql/data

Reload the service by entering:

# systemctl daemon-reload

Start PostgreSQL by entering:

# systemctl start postgresql-12

# semanage fcontext -a -t postgresql_db_t '/opt/pgsql(.*/)?'
# restorecon -Rv /opt/pgsql

Install the JDBC driver

The PageSeeder app requires a JDBC driver to communicate with PostgreSQL. Download the appropriate version of the PostgreSQL JDBC driver and save it on your server.

• On Windows it can be downloaded from: https://jdbc.postgresql.org  .
• On Linux, use the following commands (URL and version might have changed):

$ wget https://jdbc.postgresql.org/download/postgresql-42.2.18.jar

The PageSeeder installer asks for the location of the JDBC driver JAR file to be identified.

Configuration

On Linux, make the following configuration changes by editing the file postgresql.conf in the data folder (see previous section Changing where data is stored).

Add the following entries at the bottom of the file postgresql.conf file, using you own values for  shared_buffers , effective_cache_size and effective_io_concurrency as described in the previous note.

shared_buffers = 1GB
effective_cache_size = 3GB
join_collapse_limit = 12
work_mem = 16MB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
effective_io_concurrency = 2
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

After saving the file, restart PostgreSQL by entering:

# systemctl restart postgresql-12

Backup and restore

To backup the PageSeeder database on Linux, use a command like this:

$ sudo -u postgres pg_dump -Fc pageseeder > ps.backup

Restore with overwrite

To overwrite an existing database, first drop it, then recreate it using commands like this:

$ sudo -u postgres psql
drop database pageseeder;
create database pageseeder encoding 'UTF8' lc_collate = 'en_US.UTF-8' lc_ctype = 'en_US.UTF-8' template template0;
grant connect on database pageseeder to pageseeder;
\q

To restore the PageSeeder database, use commands like this:

$ sudo -u postgres pg_restore -d pageseeder ps.backup
$ sudo -u postgres psql
\c pageseeder;
analyze verbose;
\q

Restore with create

If no database or user exists, create them using commands like this:

$ sudo -u postgres psql
create database pageseeder encoding 'UTF8' lc_collate = 'en_US.UTF-8' lc_ctype = 'en_US.UTF-8' template template0;
create user pageseeder encrypted password '<password>';
grant connect on database pageseeder to pageseeder;
\q

To restore the PageSeeder database, use commands like this:

$ sudo -u postgres pg_restore -d pageseeder ps.backup
$ sudo -u postgres psql
\c pageseeder;
analyze verbose;
\q

pg_restore: error: could not execute query: ERROR:  schema "public" already exists