Managing an Advanced Server installation v13
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-13 | edb-as@13-main |
pgAgent | edb-pgagent-13 | edb-as13-pgagent |
PgBouncer | edb-pgbouncer-1.14 | edb-pgbouncer114 |
pgPool-II | edb-pgpool-4.1 | edb-pgpool41 |
Slony | edb-slony-replication-13 | edb-as13-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:
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 Advanced Server resides on version 10.x of Debian or 18.04 or 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:
Stop a service:
Restart a service:
Reload a service:
Control the component services:
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:
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 aSIGHUP
signal, reloading configuration parametersstatus
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/current/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:
Where service_name
specifies the name of the service.
Connecting to 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 Advanced Server installation.
Use the following command and options to start the edb-psql
client:
Where:
-d
specifies the database to which edb-psql
will connect.
-U
specifies the identity of the database user that will be used 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 Advanced Server features. . For more information about using the command line client, see the PostgreSQL Core Documentation at:
https://www.postgresql.org/docs/current/static/app-psql.html
Configuring a package installation
The packages that install the database server component create service startup scripts.
Creating a database cluster and starting the service
The PostgreSQL initdb
command creates a database cluster; when installing Advanced Server with an RPM package, the initdb
executable is in /usr/edb/asx.x/bin
. 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/current/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 on RHEL/Rocky Linux/AlmaLinux 8.x system, with the options specified in the service configuration file, assume the identity of the operating system superuser:
To initialize a cluster with the non-default values, you can use the PGSETUP_INITDB_OPTIONS
environment variable by invoking the edb-as-13-setup
cluster initialization script that resides under EPAS_Home/bin
.
To invoke initdb
export the PGSETUP_INITDB_OPTIONS
environment variable with the following command:
After creating the cluster, use systemctl
to start, stop, or restart the service:
On Debian 10.x | 11 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:
To start a new cluster, use the following command:
To list all the available clusters, use the following command:
Note
The data directory is created under /var/lib/edb-as/13/main2
and configuration directory is created under /etc/edb-as/13/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
.
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 for the creation, scheduling, and managing of 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 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 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 the redacting or masking of data that is 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 e-mail. |
utl_url | Provides a way to escape illegal and reserved characters within an 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 do not 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 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:
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.
If you initialize the database using "--no-redwood-compat"
mode, the installation includes the following package:
Package | Functionality non-compatible with Oracle Databases |
---|---|
dbms_aqadm | Provides supporting procedures for Advanced Queueing functionality. |
dbms_aq | Provides message queueing and processing for 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, procedures etc. |
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, 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/current/static/app-initdb.html
You can also view online help for initdb
by assuming superuser privileges and entering:
Where path_to_initdb_installation_directory
specifies the location of the initdb
binary file.
Modifying the Data Directory Location
on RHEL or CentOS 7.x | RHEL or Rocky Linux 8.x
On a RHEL/CentOS 7.x or RHEL/Rocky Linux/AlmaLinux 8.x host, the unit file is named edb-as-13.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/as13/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:After copying the unit file to the new location, create the service file
/etc/systemd/system/edb-as-13.service
.Update the following values with new location of data directory in the
/lib/systemd/system/edb-as-13.service
file:Delete the entire content of
/etc/systemd/system/edb-as-13.service
file, except the following line:Run the following command to initialize the cluster at the new location:
Use the following command to reload
systemd
, updating the modified service scripts:Start the Advanced Server service with the following command:
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 resides under /var/lib/edb/as13/data
directory. To change the default data directory location depending on individual environment preferences, you must configure the SELinux policy and perform the following steps:
Stop the server using the following command:
Check the status of SELinux using the
getenforce
orsestatus
command:Use the following command to view the SELinux context of the default database location:
Create a new directory for a new location of the database using the following command:
Use the following command to move the data directory to
/opt/edb
:Create a file
edb-as-13.service
under/etc/systemd/system
directory to include the location of a new data directory: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.Apply the context mapping using
restorecon
utility:Reload
systemd
to modify the service script using the following command:Now, the
/opt/edb
location has been labeled correctly with the context, use the following command to start the service:
Enabling Core Dumps
You can use core dumps to diagnose or debug errors. A core dump is a file containing a process's address space (memory) when the process terminates unexpectedly. Core dumps may be produced on-demand (such as by a debugger) or automatically upon termination.
Enabling Core Dumps on a RHEL/CentOS 7 or RHEL/Rocky Linux/AlmaLinux 8 Host
On a RHEL/CentOS 7.x or RHEL/Rocky Linux/AlmaLinux 8.x, core file creation is disabled by default. To enable the core file generation, follow the following commands:
Identify the system's current limit using the
ulimit -c
orulimit -a
command.0
indicates that core file generation is disabled.Create a new directory to store the core dumps and modify
kernel.core_pattern
to store the dumps in a specified directory:Use the following command to persist the
kernel.core_pattern
setting across reboots:Enable core dumps in
/etc/security/limits.conf
to allow a user to create core files. Each line describes a limit for a user in the following form:Use
*
to enable the core dump size to unlimited.Set the limit of core file size to
UNLIMITED
by using the following command:To set a core limit for the services, add the following setting in
/usr/lib/systemd/system/edb-as-13.service
.Reload the service configuration:
Modify the global default limit using
systemd
, add the following setting in/etc/systemd/system.conf
.Restart the
systemd
:Stop and then start Advanced Server:
Now, the core dumps are enabled, install the
gdb
tool and debug packages using the following command:Replace the path to a core dump file before proceeding to get a backtrace using the
bt
command to analyze output:
Enabling Core Dumps on a Debian or Ubuntu Host
On Debian 10 or Ubuntu 18, 20, core file creation is disabled by default. To enable the core file generation, follow the following commands:
Identify the system's current limit using the
ulimit -c
orulimit -a
command.0
indicates that core file generation is disabled.Create a new directory to store the core dumps and modify
kernel.core_pattern
to store the dumps in a specified directory:Use the following command to persist the
kernel.core_pattern
setting across reboots:Enable core dumps in
/etc/security/limits.conf
to allow a user to create core files. Each line describes a limit for a user in the following form:Use
*
to enable the core dump size to unlimited.Set the limit of core file size to
UNLIMITED
by using the following command:To set a core limit for the services, add the following setting in
/lib/systemd/system/edb-as@.service
.Reload the service configuration:
Modify the global default limit using
systemd
, add the following setting in/etc/systemd/system.conf
.Restart the
systemd
:Stop and then start Advanced Server:
Now, the core dumps are enabled, install the
gdb
tool and debug symbols using the following command:Replace the path to a core dump file before proceeding to get a backtrace using the
bt
command to analyze output:
Note
- The debug info packages name on a Debian or Ubuntu host may vary and include
-dbgsym
or-dbg
suffix. For more information about settingsources.list
and installing the debug info packages, visit Debian or Ubuntu wiki at https://wiki.debian.org/HowToGetABacktrace or https://wiki.ubuntu.com/Debug%20Symbol%20Packages respectively. - The core files can be huge depending on the memory usage, enabling the core dumps on a system may fill up its mass storage over time.
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-as13-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
.Edit the file, changing
PGDATA
to point to the newdata
directory that you will create the cluster against.Create the target
PGDATA
with userenterprisedb
.Run
initdb
, specifying the setup script: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:
Start the second cluster with the following command:
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 EDB 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
ordnf
to install theepel-release
,yum-utils
, andcreaterepo
packages.On RHEL or CentOS 7.x:
On RHEL or Rocky Linux or AlmaLinux 8.x:
Create a directory in which to store the repository:
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 EDB repository at https://repos.enterprisedb.com/.
Sync the RPM packages and create the repository.
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
ordnf
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:
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:
On RHEL or Rocky Linux or AlmaLinux 8:
For more information about creating a local yum
repository, visit:
- On this page
- Starting and stopping Advanced Server and supporting components
- Connecting to Advanced Server with edb-psql
- Configuring a package installation
- Specifying Cluster Options with INITDBOPTS
- Modifying the Data Directory Location
- Enabling Core Dumps
- Starting Multiple Postmasters with Different Clusters
- Creating an Advanced Server Repository on an Isolated Network