Managing an EDB Postgres Advanced Server installation v15

Suggest edits

Unless otherwise noted, the commands and paths in these instructions assume that you performed an installation using the native packages.

Starting and stopping services

A service is a program that runs in the background and doesn't require user interaction. A service provides no user interface. You can configure a service to start at boot time or manually on demand. Services are best controlled using the platform-specific operating system service control utility. Many of the EDB Postgres Advanced Server supporting components are services.

The following table lists the names of the services that control EDB Postgres Advanced Server and services that control EDB Postgres Advanced Server supporting components.

EDB Postgres Advanced Server component name	Linux service Name	Debian service name
EDB Postgres Advanced Server	edb-as-15	edb-as@15-main
pgAgent	edb-pgagent-15	edb-as15-pgagent
PgBouncer	edb-pgbouncer-1.15	edb-pgbouncer115
pgPool-II	edb-pgpool-4.1	edb-pgpool41
Slony	edb-slony-replication-15	edb-as15-slony-replication
EFM	edb-efm-4.0	edb-efm-4.0

You can use the Linux command line to control EDB Postgres Advanced Server's database server and the services of EDB Postgres Advanced Server's supporting components. The commands that control the EDB Postgres Advanced Server service on a Linux platform are host specific.

Controlling a service on RHEL/CentOS 7.x or RHEL/Rocky Linux/AlmaLinux 8.x

If your installation of EDB Postgres Advanced Server resides on version 7.x | 8.x of RHEL and CentOS, you must use the systemctl command to control the EDB Postgres Advanced Server service and supporting components.

The systemctl command must be in your search path and you must invoke it with superuser privileges. To use the command, open a command line, and enter:

systemctl <action> <service_name>

Where:

service_name specifies the name of the service.

action specifies the action taken by the service command. Specify:

start to start the service.
stop to stop the service.
restart to stop and then start the service.
status to discover the current status of the service.

Controlling a service on Debian 10.x or Ubuntu 18.04 | 20.04

If your installation of EDB Postgres Advanced Server resides on version 18.04 | 20.04 of Ubuntu, assume superuser privileges and invoke the following commands (using bundled scripts) to manage the service. Use the following commands to:

Discover the current status of a service:
```
/usr/edb/as15/bin/epas_ctlcluster 15 main status
```

Stop a service:

/usr/edb/as15/bin/epas_ctlcluster 15 main stop

Restart a service:

/usr/edb/as15/bin/epas_ctlcluster 15 main restart

Reload a service:

/usr/edb/as15/bin/epas_ctlcluster 15 main reload

Control the component services:
```
systemctl restart edb-as@15-main
```

Using pg_ctl to control EDB Postgres Advanced Server

You can use the pg_ctl utility to control an EDB Postgres Advanced Server service from the command line on any platform. pg_ctl allows you to:

Start, stop, or restart the EDB Postgres Advanced Server database server
Reload the configuration parameters
Display the status of a running server

To invoke the utility, assume the identity of the cluster owner. In the home directory of EDB Postgres Advanced Server, issue the command:

./bin/pg_ctl -D <data_directory> <action>

data_directory is the location of the data controlled by the EDB Postgres Advanced Server cluster.

action specifies the action taken by the pg_ctl utility. Specify:

start to start the service.
stop to stop the service.
restart to stop and then start the service.
reload to send the server a SIGHUP signal, reloading configuration parameters
status to discover the current status of the service.

For more information about using the pg_ctl utility or the command line options available, see the PostgreSQL core documentation.

Choosing between pg_ctl and the service command

You can use the pg_ctl utility to manage the status of an EDB Postgres Advanced Server cluster. However, it's important to note that pg_ctl doesn't alert the operating system service controller to changes in the status of a server. We recommend using the service command when possible.

Configuring component services to autostart at system reboot

After installing, configuring, and starting the services of EDB Postgres Advanced Server supporting components on a Linux system, you must manually configure your system to autostart the service when your system restarts. To configure a service to autostart on a Linux system, open a command line, assume superuser privileges, and enter the command.

On a Redhat-compatible Linux system, enter:

/sbin/chkconfig <service_name> on

Where service_name specifies the name of the service.

Connecting to EDB Postgres Advanced Server with edb-psql

edb-psql is a command line client application that allows you to execute SQL commands and view the results. To open the edb-psql client, the client must be in your search path. The executable resides in the bin directory under your EDB Postgres Advanced Server installation.

Use the following command and options to start the edb-psql client:

psql -d edb -U enterprisedb

Where:

-d specifies the database to which edb-psql connects.

-U specifies the identity of the database user to use for the session.

edb-psql is a symbolic link to a binary called psql, a modified version of the PostgreSQL community psql, with added support for EDB Postgres Advanced Server features. For more information about using the command line client, see the PostgreSQL core documentation.

Managing authentication on a Debian or Ubuntu host

By default, the server is running with the peer or md5 permission on a Debian or Ubuntu host. You can change the authentication method by modifying the pg_hba.conf file, located under /etc/edb-as/15/main/.

For more information about modifying the pg_hba.conf file, see the PostgreSQL core documentation.

Configuring a package installation

The packages that install the database server component create a unit file on version 7.x or 8.x hosts and service startup scripts.

Creating a database cluster and starting the service

The PostgreSQL initdb command creates a database cluster. When installing EDB Postgres Advanced Server with an RPM package, the initdb executable is in /usr/edb/asx.x/bin. After installing EDB Postgres Advanced Server, you must manually configure the service and invoke initdb to create your cluster. When invoking initdb, you can:

Specify environment options on the command line.
Include the systemd service manager on RHEL/CentOS 7.x or RHEL/Rocky Linux/AlmaLinux 8.x and use a service configuration file to configure the environment.

For more information, see the initdb documentation.

After specifying any options in the service configuration file, you can create the database cluster and start the service. The steps are platform specific.

On RHEL/CentOS 7.x or RHEL/Rocky Linux/AlmaLinux 8.x

To invoke initdb on a RHEL/CentOS 7.x or Rocky Linux/AlmaLinux 8.x system with the options specified in the service configuration file, assume the identity of the operating system superuser:

su - root

To initialize a cluster with the non-default values, you can use the PGSETUP_INITDB_OPTIONS environment variable. You can initialize the cluster using the edb-as-15-setup script under EPAS_Home/bin.

To invoke initdb, export the PGSETUP_INITDB_OPTIONS environment variable:

PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/as15/bin/edb-as-15-setup initdb

After creating the cluster, use systemctl to start, stop, or restart the service:

systemctl { start | stop | restart } edb-as-15

On Debian 10.x or Ubuntu 18.04 | 20.04

You can initialize multiple clusters using the bundled scripts. To create a new cluster, assume root privileges, and invoke the bundled script:

/usr/bin/epas_createcluster 15 main2

To start a new cluster:

/usr/bin/epas_ctlcluster 15 main2 start

To list all the available clusters:

/usr/bin/epas_lsclusters

Note

The data directory is created under /var/lib/edb-as/15/main2, and the configuration directory is created under /etc/edb-as/15/main/.

Specifying cluster options with INITDBOPTS

You can use the INITDBOPTS variable to specify your cluster configuration preferences. By default, the INITDBOPTS variable is commented out in the service configuration file. Unless you modify it, when you run the service startup script, the new cluster is created in a mode compatible with Oracle databases. Clusters created in this mode contain a database named edb and have a database superuser named enterprisedb.

Initializing the cluster in Oracle mode

If you initialize the database using Oracle compatibility mode, the installation includes:

Data dictionary views compatible with Oracle databases.
Oracle data type conversions.
Date values displayed in a format compatible with Oracle syntax.
Support for Oracle-styled concatenation rules. If you concatenate a string value with a NULL value, the returned value is the value of the string.
Support for the following Oracle built-in packages.

Package	Functionality compatible with Oracle databases
`dbms_alert`	Provides the capability to register for, send, and receive alerts.
`dbms_job`	Provides the capability to create, schedule, and manage jobs.
`dbms_lob`	Provides the capability to manage on large objects.
`dbms_output`	Provides the capability to send messages to a message buffer or get messages from the message buffer.
`dbms_pipe`	Provides the capability to send messages through a pipe within or between sessions connected to the same database cluster.
`dbms_rls`	Enables the implementation of Virtual Private Database on certain EDB Postgres Advanced Server database objects.
`dbms_sql`	Provides an application interface to the EDB dynamic SQL functionality.
`dbms_utility`	Provides various utility programs.
`dbms_aqadm`	Provides supporting procedures for Advanced Queueing functionality.
`dbms_aq`	Provides message queueing and processing for EDB Postgres Advanced Server.
`dbms_profiler`	Collects and stores performance information about the PL/pgSQL and SPL statements that are executed during a performance profiling session.
`dbms_random`	Provides a number of methods to generate random values.
`dbms_redact`	Enables redacting or masking data that's returned by a query.
`dbms_lock`	Provides support for the `DBMS_LOCK.SLEEP` procedure.
`dbms_scheduler`	Provides a way to create and manage jobs, programs, and job schedules.
`dbms_crypto`	Provides functions and procedures to encrypt or decrypt RAW, BLOB or CLOB data. You can also use `DBMS_CRYPTO` functions to generate cryptographically strong random values.
`dbms_mview`	Provides a way to manage and refresh materialized views and their dependencies.
`dbms_session`	Provides support for the `DBMS_SESSION.SET_ROLE` procedure.
`utl_encode`	Provides a way to encode and decode data.
`utl_http`	Provides a way to use the HTTP or HTTPS protocol to retrieve information found at an URL.
`utl_file`	Provides the capability to read from and write to files on the operating system’s file system.
`utl_smtp`	Provides the capability to send e-mails over the Simple Mail Transfer Protocol (SMTP).
`utl_mail`	Provides the capability to manage email.
`utl_url`	Provides a way to escape illegal and reserved characters in a URL.
`utl_raw`	Provides a way to manipulate or retrieve the length of raw data types.

Initializing the cluster in Postgres mode

Clusters created in PostgreSQL mode don't include compatibility features. To create a new cluster in PostgreSQL mode, remove the pound sign (#) in front of the INITDBOPTS variable, enabling the "--no-redwood-compat" option. Clusters created in PostgreSQL mode contain a database named postgres and have a database superuser named postgres.

You can also specify multiple initdb options. For example, the following statement creates a database cluster without compatibility features for Oracle. The cluster contains a database named postgres that's owned by a user named alice. The cluster uses UTF-8 encoding.

INITDBOPTS="--no-redwood-compat -U alice --locale=en_US.UTF-8"

If you initialize the database using "--no-redwood-compat" mode, the installation includes the following package:

Package	Functionality noncompatible with Oracle databases
`dbms_aqadm`	Provides supporting procedures for Advanced Queueing functionality.
`dbms_aq`	Provides message queueing and processing for EDB Postgres Advanced Server.
`edb_bulkload`	Provides direct/conventional data loading capability when loading huge amount of data into a database.
`edb_gen`	Provides miscellaneous packages to run built-in packages.
`edb_objects`	Provides Oracle-compatible objects such as packages and procedures.
`waitstates`	Provides monitor session blocking.
`edb_dblink_libpq`	Provides link to foreign databases via libpq.
`edb_dblink_oci`	Provides link to foreign databases via OCI.
`snap_tables`	Creates tables to hold wait information. Included with DRITA scripts.
`snap_functions`	Creates functions to return a list of snap ids and the time the snapshot was taken. Included with DRITA scripts.
`sys_stats`	Provides OS performance statistics.

In addition to the cluster configuration options documented in the PostgreSQL core documentation, EDB Postgres Advanced Server supports the following initdb options:

--no-redwood-compat

Include the --no-redwood-compat keywords to create the cluster in PostgreSQL mode. When the cluster is created in PostgreSQL mode, the name of the database superuser is postgres, and the name of the default database is postgres. The few EDB Postgres Advanced Server features compatible with Oracle databases are available with this mode. However, we recommend using the EDB Postgres Advanced Server in redwood compatibility mode to use all its features.

--redwood-like

Include the --redwood-like keywords to use an escape character, that is, an empty string (''), following the LIKE (or PostgreSQL-compatible ILIKE) operator in a SQL statement that's compatible with Oracle syntax.

--icu-short-form

Include the --icu-short-form keywords to create a cluster that uses a default International Components for Unicode (ICU) collation for all databases in the cluster. For more information about Unicode collations, see Unicode collation algorithm.

For more information about using initdb and the available cluster configuration options, see the PostgreSQL core documentation.

You can also view online help for initdb by assuming superuser privileges and entering:

/path_to_initdb_installation_directory/initdb --help

Where path_to_initdb_installation_directory specifies the location of the initdb binary file.

Modifying the data directory location

On RHEL/CentOS 7.x or RHEL/Rocky Linux/AlmaLinux 8.x

On a RHEL/CentOS 7.x or RHEL/Rocky Linux/AlmaLinux 8.x host, the unit file is named edb-as-15.service and resides in /usr/lib/systemd/system. The unit file contains references to the location of the EDB Postgres Advanced Server data directory. Avoid making any modifications directly to the unit file because they might be overwritten during package upgrades.

By default, data files reside under /var/lib/edb/as15/data directory. To use a data directory that resides in a non-default location:

Create a copy of the unit file under the /etc directory:
```
cp /usr/lib/systemd/system/edb-as-15.service /etc/systemd/system/
```
After copying the unit file to the new location, create the service file /etc/systemd/system/edb-as-15.service.
Update the following values with new location of data directory in the /lib/systemd/system/edb-as-15.service file:
```
Environment=PGDATA=/var/lib/edb/as15/data
PIDFile=/var/lib/edb/as15/data/postmaster.pid
```
Delete the content of the /etc/systemd/system/edb-as-15.service file except the following line:
```
.include /lib/systemd/system/edb-as-15.service
```
Run the following command to initialize the cluster at the new location:
```
PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/as15/bin/edb-as-15-setup initdb
```
Use the following command to reload systemd, updating the modified service scripts:
```
systemctl daemon-reload
```
Start the EDB Postgres Advanced Server service with the following command:
```
systemctl start edb-as-15
```

Configuring SELinux policy to change the data directory location on RHEL/CentOS 7.x or RHEL/Rocky Linux/AlmaLinux 8.x

By default, the data files reside under the /var/lib/edb/as15/data directory. To change the default data directory location depending on individual environment preferences, you must configure the SELinux policy:

Stop the server:
```
systemctl stop edb-as-15
```

Check the status of SELinux using the getenforce or sestatus command:

# getenforce
Enforcing

# sestatus
SELinux status:                 enabled
SELinuxfs mount:                /sys/fs/selinux
SELinux root directory:         /etc/selinux
Loaded policy name:             targeted
Current mode:                   enforcing
Mode from config file:          enforcing
Policy MLS status:              enabled
Policy deny_unknown status:     allowed
Max kernel policy version:      31

View the SELinux context of the default database location:

ls -lZ /var/lib/edb/as15/data
drwx------. enterprisedb enterprisedb unconfined_u:object_r:var_lib_t:s0 log

Create a new directory for a new location of the database:
```
mkdir /opt/edb
```
Move the data directory to /opt/edb:
```
mv /var/lib/edb/as15/data /opt/edb/
```

Create a file edb-as-15.service under /etc/systemd/system to include the location of a new data directory:

.include /lib/systemd/system/edb-as-15.service
[Service]
Environment=PGDATA=/opt/edb/data
PIDFile=/opt/edb/data/postmaster.pid

Use the semanage utility to set the context mapping for /opt/edb/. The mapping is written to /etc/selinux/targeted/contexts/files/file.contexts.local file.
```
semanage fcontext --add --equal /var/lib/edb/as15/data /opt/edb
```
Apply the context mapping using the restorecon utility:
```
restorecon -rv /opt/edb/
```
Reload systemd to modify the service script :
```
systemctl daemon-reload
```
With the /opt/edb location labeled correctly with the context, start the service:
```
systemctl start edb-as-15
```

Starting multiple postmasters with different clusters

You can configure EDB Postgres Advanced Server to use multiple postmasters, each with its own database cluster. The steps required are version specific to the Linux host.

On RHEL/CentOS 7.x or RHEL/Rocky Linux/AlmaLinux 8.x

The edb-as15-server-core RPM for version 7.x | 8.x contains a unit file that starts the EDB Postgres Advanced Server instance. The file allows you to start multiple services, with unique data directories and monitor different ports. You need to have root access to invoke or modify the script.

This example creates an EDB Postgres Advanced Server installation with two instances. The secondary instance is named secondary.

Make a copy of the default file with the new name. As noted at the top of the file, all modifications must reside under /etc. You must pick a name that isn't already used in /etc/systemd/system.
```
cp /usr/lib/systemd/system/edb-as-15.service /etc/systemd/system/secondary-edb-as-15.service
```
Edit the file, changing PGDATA to point to the new data directory that you'll create the cluster against.
Create the target PGDATA with user enterprisedb.

Run initdb, specifying the setup script:

/usr/edb/as15/bin/edb-as-15-setup initdb secondary-edb-as-15

Edit the postgresql.conf file for the new instance, specifying the port, the IP address, TCP/IP settings, and so on.
Make sure that the new cluster runs after a reboot:
```
systemctl enable secondary-edb-as-15
```
Start the second cluster:
```
systemctl start secondary-edb-as-15
```

← Prev

Installing on Linux using a local repository