pgBackrest, an open source backup and restore solution for PostgreSQL, is included with VMware Postgres. You can configure PostgreSQL and pgBackRest to set up backup and restore for your Postgres databases.
Install pgBackRest Perl prerequisites.
$ sudo yum install perl perl-Time-HiRes perl-Digest-SHA perl-JSON-PP
Create pgBackRest configuration directories and files.
$ sudo mkdir -p /etc/pgbackrest $ sudo mkdir -p /etc/pgbackrest/conf.d $ sudo touch /etc/pgbackrest/pgbackrest.conf $ sudo chmod 640 /etc/pgbackrest/pgbackrest.conf $ sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
Check that pgBackRest is properly installed.
$ sudo -u postgres pgbackrest
You will see errors if any dependencies are missing.
See the pgBackRest User Guide for help configuring Postgres and pgBackRest, and for backing up and restoring your Postgres databases.
VMware Postgres includes the PostgreSQL extension pgAudit, that allows users to monitor specific operations and objects. pgAudit enhances the native Postgres logging abilities with improved log formatting and filtering operations, that assists with regulation compliance.
Use the following table as a reference of PostgreSQL to pgAudit version mapping:
|PostgresSQL version||pgAudit version|
Install pgAudit using the appropriate package name for your VMware Postgres. For example:
sudo yum install vmware-postgres14-pgaudit-extension-1.6.1-1.el7.src.rpm
Add pgAudit to the
shared_preload_libraries in the postgresql.conf configuration file. For example:
shared_preload_libraries = 'pgaudit'
Restart the database to apply the change into Postgres.
Enable the pgAudit extension:
CREATE EXTENSION pgaudit;
Verify the installation using a command similar to:
postgres=# select count(*) from pg_extension where extname = 'pgaudit';
count ------- 1 (1 row)
Session logging will log operations performend by a specific user. Use the parameter
pgaudit.log to start auditing, depending on your requirements.
To view the default pgAudit parameters, log into your database, and use the system view
pg_settings in a command similar to:
postgres=# SELECT name,setting FROM pg_settings WHERE name LIKE 'pgaudit%';
which returns an output similar to:
name | setting ---------------------------+--------- pgaudit.log | none pgaudit.log_catalog | on pgaudit.log_client | off pgaudit.log_level | log pgaudit.log_parameter | off pgaudit.log_relation | off pgaudit.log_rows | off pgaudit.log_statement | on pgaudit.log_statement_once | off pgaudit.role | (10 rows)
Use the configuration parameter
pgaudit.log, and a comma-separated list, to specify the classes of statements that will be logged in session mode. The possible values are: READ, WRITE, FUNCTION, ROLE, DDL, MISC, MISC_SET, ALL and NONE. The default is none.
For details on each of the options, see pgaudit.log in the official pgAudit documentation.
pgaudit.log in postgresql.conf to apply globally, use
ALTER DATABASE <your-database> SET pgaudit.log = '<your-values>' to monitor per-database, and use
ALTER ROLE <your-database> SET pgaudit.log = '<your-values>' to monitor per-user.
Inspect the Postgres log files to confirm logging works as expected.
To audit all the reads, and writes, run:
postgres=# set pgaudit.log = 'read, write';
To create a separate log entry for each relation reference in a statement, use:
postgres=# set pgaudit.log_relation=on
Object logging will audit all actions performed on a specific object, using the parameter
pgaudit.role. Object mode supports SELECT, INSERT, UPDATE, and DELETE statements only.
Use object logging when you wish restrict logging to a subset of objects, and avoid monitoring all the session level information. In order to leverage specific object auditing you need to create an auditor role and grant that role on the objects you wish to audit.
To log every social security number read by anyone with the role auditor_role assigned, while avoiding logging everything with a session-level audit log, use:
CREATE ROLE auditor_role; GRANT SELECT (social_security_number) ON payroll.employees TO auditor_role; /* Assign auditor_role to applicable users */ SET pgaudit.role = 'auditor_role'; SET pgadut.log = 'read';
For further details and examples, see Object Audit Logging in the pgAudit documentation.
You configure psqlODBC by placing settings into a configuration file,
odbc.ini, and then using an ODBC driver manager such as UnixODBC or iODBC to load the configuration file. See 7.3. Configuration Files in the PostgreSQL documentation for information about the ODBC driver configuration settings.
See also psqlODBC - PostgreSQL ODBC driver for additional HOWTOs and release-specific information associated with the driver software.
In order to use the
pgjdbc driver from a Java application, you must include the driver software in your CLASSPATH as described in Setting up the Class Path. See also the full documentation at The PostgreSQL JDBC Interface for information about initializing and using the JDBC driver in your Java applications.