Managing an Advanced Server Installation v11

Suggest edits

Unless otherwise noted, the commands and paths noted in the following section assume that you have performed an installation using the native packages.

Starting and Stopping Advanced Server and Supporting Components

A service is a program that runs in the background and requires no user interaction (in fact, a service provides no user interface); a service can be configured 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 Advanced Server supporting components are services.

The following table lists the names of the services that control Advanced Server and services that control Advanced Server supporting components:

Advanced Server Component Name	Linux Service Name	Debian Service Name
Advanced Server	edb-as-11	edb-as@11-main
pgAgent	edb-pgagent-11	edb-as11-pgagent
PgBouncer	edb-pgbouncer-1.14	edb-pgbouncer114
pgPool-II	edb-pgpool-4.1	edb-pgpool41
Slony	edb-slony-replication-11	edb-as11-slony-replication
EFM	edb-efm-4.0	edb-efm-4.0

You can use the Linux command line to control Advanced Server's database server and the services of Advanced Server's supporting components. The commands that control the 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 Advanced Server resides on RHEL/CentOS 7.x or RHEL/Rocky Linux/AlmaLinux 8.x, you must use the systemctl command to control the Advanced Server service and supporting components.

The systemctl command must be in your search path and must be invoked 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 Ubuntu 18.04

By default, a server is running with the peer or md5 permission on a Debian or Ubuntu host. You can change the auth_type from pg_hba.conf file available under:

vi /etc/edb-as/11/main/pg_hba.conf

Please note, all the server configuration files are available under /etc/edb-as/11/main.

If your installation of Advanced Server resides on version 18.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/bin/epas_ctlcluster 11 main status
```
Stop a service:
```
/usr/bin/epas_ctlcluster 11 main stop
```

Restart a service:

/usr/bin/epas_ctlcluster 11 main restart

Reload a service:
```
/usr/bin/epas_ctlcluster 11 main reload
```
Control the component services:
```
systemctl restart edb-as@11-main
```

Using pg_ctl to Control Advanced Server

You can use the pg_ctl utility to control an Advanced Server service from the command line on any platform. pg_ctl allows you to start, stop, or restart the Advanced Server database server, reload the configuration parameters, or display the status of a running server. To invoke the utility, assume the identity of the cluster owner, navigate into the home directory of Advanced Server, and issue the command:

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

data_directory is the location of the data controlled by the 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 sends 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 official PostgreSQL Core Documentation available at:

https://www.postgresql.org/docs/11/static/app-pg-ctl.html

Choosing Between pg_ctl and the service Command

You can use the pg_ctl utility to manage the status of an Advanced Server cluster, but it is important to note that pg_ctl does not alert the operating system service controller to changes in the status of a server, so it is beneficial to use the service command whenever possible.

Configuring Component Services to AutoStart at System Reboot

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

On a Redhat-compatible Linux system, enter:

/sbin/chkconfig <service_name> on

Where service_name specifies the name of the service.

Connecting to Advanced Server with psql

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

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

psql -d edb -U enterprisedb

Where:

-d specifies the database to which psql will connect.

-U specifies the identity of the database user that will be used for the session.

For more information about using the command line client, see the PostgreSQL Core Documentation at:

https://www.postgresql.org/docs/11/static/app-psql.html

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. After installing 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.

To review the initdb documentation, visit:

https://www.postgresql.org/docs/11/static/app-initdb.html

After specifying any options in the service configuration file, you can create the database cluster and start the service; these 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 RHEL/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 by invoking the edb-as-11-setup cluster initialization script that resides under EPAS_Home/bin.

To invoke initdb export the PGSETUP_INITDB_OPTIONS environment variable with the following command:

PGSETUP_INITDB_OPTIONS="-E UTF-8" /usr/edb/as11/bin/edb-as-11-setup initdb

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

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

On Ubuntu 18.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 11 main2

To start a new cluster, use the following command:

/usr/bin/epas_ctlcluster 11 main2 start

To list all the available clusters, use the following command:

/usr/bin/epas_lsclusters

Note

The data directory is created under /var/lib/edb-as/11/main2 and configuration directory is created under /etc/edb-as/11/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 modified, when you run the service startup script, the new cluster will be created in a mode compatible with Oracle databases. Clusters created in this mode will contain a database named edb, and have a database superuser named enterprisedb.

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 will contain a database named postgres, and have a database superuser named postgres.

You may also specify multiple initdb options. For example, the following statement:

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

Creates a database cluster (without compatibility features for Oracle) that contains a database named postgres that is owned by a user named alice; the cluster uses UTF-8 encoding.

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

--no-redwood-compat

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

--redwood-like

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

--icu-short-form

Include the --icu-short-form keywords to create a cluster that uses a default ICU (International Components for Unicode) collation for all databases in the cluster. For more information about Unicode collations, refer to the EDB Postgres Advanced Server Guide available at:

https://www.enterprisedb.com/docs

For more information about using initdb, and the available cluster configuration options, see the PostgreSQL Core Documentation available at:

https://www.postgresql.org/docs/11/static/app-initdb.html

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-11.service and resides in /usr/lib/systemd/system. The unit file contains references to the location of the Advanced Server data directory. You should avoid making any modifications directly to the unit file because it may be overwritten during package upgrades.

By default, data files reside under /var/lib/edb/as11/data directory. To use a data directory that resides in a non-default location, perform the following steps:

Create a copy of the unit file under the /etc directory:
```
cp /usr/lib/systemd/system/edb-as-11.service /etc/systemd/system/
```
After copying the unit file to the new location, modify the service file /etc/systemd/system/edb-as-11.service with your editor of choice, correcting any required paths.
Use the following command to reload systemd, updating the modified service scripts:
```
systemctl daemon-reload
```
Start the Advanced Server service with the following command:
```
systemctl start edb-as-11
```

Starting Multiple Postmasters with Different Clusters

You can configure 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-as11-server-core RPM for version 7.x | 8.x contains a unit file that starts the Advanced Server instance. The file allows you to start multiple services, with unique data directories and that monitor different ports. You must have root access to invoke or modify the script.

The example that follows creates an 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 is not already used in /etc/systemd/system.
```
cp /usr/lib/systemd/system/edb-as-11.service /etc/systemd/system/secondary-edb-as-11.service
```
Edit the file, changing PGDATA to point to the new data directory that you will create the cluster against.
Create the target PGDATA with user enterprisedb.

Run initdb, specifying the setup script:

/usr/edb/as11/bin/edb-as-11-setup initdb secondary-edb-as-11

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

Creating an Advanced Server Repository on an Isolated Network

You can create a local repository to act as a host for the Advanced Server RPM packages if the server on which you wish to install Advanced Server (or supporting components) cannot directly access the EnterpriseDB repository. Please note that this is a high-level listing of the steps requires; you will need to modify the process for your individual network.

To create and use a local repository, you must:

Use yum or dnf to install the epel-release, yum-utils, and createrepo packages.
On RHEL or CentOS 7.x:
```
yum install epel-release
yum install yum-utils
yum install createrepo
```
On RHEL or Rocky Linux/AlmaLinux 8.x:
```
dnf install epel-release
dnf install yum-utils
dnf install createrepo
```
Create a directory in which to store the repository:
```
mkdir /srv/repos
```
Copy the RPM installation packages to your local repository. You can download the individual packages or use a tarball to populate the repository. The packages are available from the EnterpriseDB repository at https://repos.enterprisedb.com/.
Sync the RPM packages and create the repository.
```
reposync -r edbas11 -p /srv/repos
createrepo /srv/repos
```
Install your preferred webserver on the host that will act as your local repository, and ensure that the repository directory is accessible to the other servers on your network.
On each isolated database server, configure yum or dnf to pull updates from the mirrored repository on your local network. For example, you might create a repository configuration file called /etc/yum.repos.d/edb-repo with connection information that specifies:
```
[edbas11]
name=EnterpriseDB Advanced Server 11
baseurl=https://yum.your_domain.com/edbas11
enabled=1
gpgcheck=0
```

After specifying the location and connection information for your local repository, you can use yum or dnf commands to install Advanced Server and its supporting components on the isolated servers. For example:

On RHEL or CentOS 7:
```
yum -y install edb-as11-server
```
On RHEL or Rocky Linux/AlmaLinux 8:
```
dnf -y install edb-as11-server
```

For more information about creating a local yum repository, visit:

https://wiki.centos.org/HowTos/CreateLocalRepos

← Prev

Available native packages

Windows installation details

Managing an Advanced Server Installation v11

Starting and Stopping Advanced Server and Supporting Components

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

Controlling a Service on Ubuntu 18.04

Using pg_ctl to Control Advanced Server

Configuring Component Services to AutoStart at System Reboot

Connecting to Advanced Server with psql

Configuring a Package Installation

Creating a Database Cluster and Starting the Service

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

On Ubuntu 18.04

Note

Specifying Cluster Options with INITDBOPTS

Modifying the Data Directory Location on RHEL/CentOS 7.x or RHEL/Rocky Linux/AlmaLinux 8.x

Starting Multiple Postmasters with Different Clusters

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

Creating an Advanced Server Repository on an Isolated Network

← Prev

Next →