We recently launched version 15 of Fujitsu Enterprise Postgres, and I thought that this would be a great opportunity to discuss the new OSS features offered and inherited in this Fujitsu Enterprise Postgres release which are on top of the exclusively Fujitsu developed enterprise grade features as introduced by Marcos Figueredo in his recent blog.
In this post I describe the additional OSS features, alongside their benefits, with examples to walk you through the improved functionality.
Contents
> Removal of CREATE permission on the PUBLIC schema
> Changes related to the checkpoint functionality
> New predefined role pg_checkpoint
> Default value of the log_checkpoints parameter changed to 'on'
> Enhancement in pg_basebackup
> Enhancement in Logical Replication
> Allow publication of all tables in a schema
> Allow publication content to be filtered using a WHERE clause
> Allow publications to be restricted to specific columns
> Support of jsonlog in log_destination parameter
Removal of the CREATE permission on the PUBLIC schema
Prior to version 15, all users were given CREATE and USAGE access by default in the PUBLIC schema, which posed a security risk. This risk is mitigated in the new release, since only the database owner has default rights to the CREATE and USAGE privileges in the PUBLIC schema.
Additionally, the owner of the public schema has been changed to pg_database_owner. As a result of this update, the recommended behavior stated in the security release for CVE-2018-1058 is now achieved.
See below the differences when a new user tries to create a table in the PUBLIC schema.
Version 14
postgres=# CREATE USER test PASSWORD 'fep123';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------+------------------------------------------------------------+-----------
fsepuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
postgres=# \c postgres test
You are now connected to database "postgres" as user "test".
postgres=> CREATE TABLE test (id numeric);
CREATE TABLE
Version 15
postgres=# CREATE USER test PASSWORD 'fep123';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------+------------------------------------------------------------+-----------
fsepuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
postgres=# \c postgres test
You are now connected to database "postgres" as user "test".
postgres=> CREATE TABLE test (id numeric);
ERROR: permission denied for schema public
LINE 1: create table test(id numeric);
Changes related to the checkpoint functionality
New predifined role pg_checkpoint
Prior to version 15, only superusers were permitted to execute checkpoint operations. Now, users can run CHECKPOINT thanks to a new role named pg_checkpoint. Any non-superuser who is assigned this role can run checkpoint commands.
Version 14
postgres=# CREATE USER test_chkpt PASSWORD 'fep123';
CREATE ROLE
postgres=# \c postgres test_chkpt
You are now connected to database "postgres" as user "test_chkpt".
postgres=> CHECKPOINT;
ERROR: must be superuser to do CHECKPOINT (11116)
postgres=> \du
List of roles
Role name | Attributes | Member of
----------+------------------------------------------------------------+-----------
fsepuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
test_chkpt| | {}
Version 15
postgres=# CREATE USER test_chkpt PASSWORD 'fep123';
CREATE ROLE
postgres=#
postgres=# GRANT pg_checkpoint to test_chkpt;
GRANT ROLE
postgres=# \c postgres test_chkpt
You are now connected to database "postgres" as user "test_chkpt".
postgres=>
postgres=> CHECKPOINT;
CHECKPOINT
postgres=> \du
List of roles
Role name | Attributes | Member of
----------+------------------------------------------------------------+-----------
fsepuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test | | {}
test_chkpt| | {pg_checkpoint}
Default value of the log_checkpoints parameter changed to 'on'
Prior to version 15, the log_checkpoints parameter in postgresql.conf was set to 'off' by default. This default setting has been changed to 'on' now.
With this feature, checkpoints and restart points will now be recorded in the database server log file. Certain statistics, such as the number of buffers written and the time spent writing them, will be provided in the log messages. Even an inactive server will generate some log output in the server log file as a result of this.
Support of MERGE command
The INSERT, DELETE, and UPDATE commands on a table or a partition of tables are executed simultaneously by the MERGE statement in FEP-15 based on the join condition. The WHEN MATCHED clause can be used to update (UPDATE), remove (DELETE), or do nothing (DO NOTHING) operations on the tuples that match the join condition.
It is also possible to specify additional requirements. The WHEN NOT MATCHED clause describes what happens to the tuples when the condition is not met.
The MERGE command does not currently support foreign tables or updatable views. Based on the future requirements, this will most likely be added soon.
Let’s say we have two different tables as below.
airline=# SELECT * from aircrafts_data;
aircraft_code| model | range
-------------+---------------------+-------
773 | Boeing 777-300 | 11100
763 | Boeing 767-300 | 7900
SU9 | Sukhoi Suprejet-100 | 3000
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
733 | Boeing 737-300 | 4200
CN1 | Cessna 208 Caravan | 1200
CR2 | Bombardier CRJ-200 | 2700
(9 rows)
airline=# SELECT* from aircrafts_data_new;
aircraft_code| model | range
-------------+---------------------+-------
773 | Boeing 777-300 | 11100
763 | Boeing 767-300 | 7900
SU9 | Sukhoi Suprejet-100 | 3000
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
733 | Boeing 737-300 | 4200
CN1 | Cessna 208 Caravan | 1200
CR2 | Bombardier CRJ-200 | 2700
523 | Airbus 777-300 | 7900
504 | Airbus 350-900 | 9000
528 | Boeing 787-10 | 10000
(12 rows)
Now, we will merge the two tables using the syntax below. Here, we want to add only the new entries while keeping the old entries as they are if the data in the model column is the same.
airline=# MERGE INTO aircrafts_data AS c
airline=# USING aircrafts_data_new AS n
airline=# ON c.aircraft_code = n.aircraft_code
airline=# WHEN MATCHED AND c.model = n.model THEN
airline=# DO NOTHING
airline=# WHEN MATCHED AND c.model <> n.model THEN
airline=# UPDATE SET model=n.model
airline=# WHEN NOT MATCHED THEN
airline=# INSERT VALUES (n.aircraft_code, n.model, n.range)
airline=# ;
MERGE 12
airline=#
Now, let’s execute the SELECT on the original aircrafts_data table to verify that the MERGE statement executes effectively or not.
airline=# select * from aircrafts_data;
aircraft_code| model | range
-------------+---------------------+-------
773 | Boeing 777-300 | 11100
763 | Boeing 767-300 | 7900
SU9 | Sukhoi Suprejet-100 | 3000
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
733 | Boeing 737-300 | 4200
CN1 | Cessna 208 Caravan | 1200
CR2 | Bombardier CRJ-200 | 2700
523 | Airbus 777-300 | 7900
504 | Airbus 350-900 | 9000
528 | Boeing 787-10 | 10000
(12 rows)
We can see that with the help of the MERGE command, we effectively merged two different tables.
Enhancement in pg_basebackup
As we all very well know, pg_basebackup is important for taking a base backup of a PostgreSQL cluster. The new version introduces these enhancements.
- Option --target: Designates the server where the base backup will be stored.
This option makes use of the BASE BACKUP TARGET data transmission protocol. The output directory on the server can be specified using the server:/path formats.
A user with the SUPERUSER or pg_write_server_files roles can run server-side backups. This option cannot be combined with -Xstream since pg_basebackup, rather than the server, implements WAL streaming. Since it is the default WAL mechanism, you must specify either -Xfetch or -Xnone when this option is used. - Option --compress: Specifies compression level and method - for example, gzip:9.
To specify where to compress the backup, this option also takes the client-gzip and server-gzip compression methods. The ability to utilize compression with plain format provides you the capability to automatically compress your pg_basebackup on the server and extract it again on the client side, which is another benefit of this option. This is quite helpful for situations where slow network connections are used.
Example:
pg_basebackup -h localhost -p 27500 -Ft --compress=client-gzip:9 –D /backup/<dir_date>/ -Xs
Enhancement in logical replication
Logical replication is a method of replicating data objects and their changes, based on their replication identity (usually a primary key). We use the term logical in contrast to physical replication, which uses exact block addresses and byte-by-byte replication.
Ability to publish all tables in a schema
As of version 14, it was necessary to list each table name from the schema one at a time when setting up logical replication and constructing the PUBLICATION, which was laborious and time-consuming. While it was possible to add all tables from all schemas, it was not possible to add schema-specific tables.
As of version 15, the following syntax can be used when generating the PUBLICATION to add all tables of a certain schema for logical replication, saving a lot of time.
CREATE PUBLICATION <pub-name>
FOR TABLES IN SCHEMA <schema-1>, <schema-2>, <schema-n>;
Version 14
airline=# \dn
List of schemas
Name | Owner
----------|----------
bookings | fsepuser
public | fsepuser
(2 rows)
airline=# CREATE PUBLICATION pub1 FOR ALL TABLES;
CREATE PUBLICATION
airline=#
airline=# SELECT pubname, schemaname, tablename FROM pg_publication_tables;
pubname | schemaname | tablename
---------+-------------+-----------------
pub1 | bookings | boarding_passes
pub1 | public | test
pub1 | bookings | aircrafts_data
pub1 | bookings | flights
pub1 | bookings | airports_data
pub1 | bookings | seats
pub1 | bookings | ticket_flights
pub1 | bookings | tickets
pub1 | bookings | bookings
(9 rows)
Version 15
airline=# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA bookings;
CREATE PUBLICATION
airline=#
airline=# SELECT pubname, schemaname, tablename FROM pg_publication_tables;
pubname | schemaname | tablename
---------+-------------+-----------------
pub1 | bookings | ticket_flights
pub1 | bookings | boarding_passes
pub1 | bookings | aircrafts_data
pub1 | bookings | flights
pub1 | bookings | airports_data
pub1 | bookings | seats
pub1 | bookings | tickets
pub1 | bookings | bookings
(8 rows)
Ability to filter publication content using a WHERE clause
Both CREATE PUBLICATION and ALTER PUBLICATION now allow the WHERE clause, which can be used to limit the replication of tuples.
We must provide the replication conditions for each table in order to use this capability. Parentheses must surround the description of the WHERE clause.
airline=# ALTER PUBLICATION pub1 FOR TABLE aircrafts_data WHERE (range >= 5000);
ALTER PUBLICATION
Allow publications to be restricted to specific columns
In version 15, by generating a PUBLICATION as shown below, it is possible to replicate only particular columns of a table. Based on your requirements, we must specify a list of columns for the table name in the CREATE PUBLICATION or ALTER PUBLICATION command.
airline=# CREATE PUBLICATION pub2 FOR TABLE aircrafts_data (aircraft_code,model);
CREATE PUBLICATION
Support of jsonlog in log_destination parameter
Like other relational database management systems (RDBMS), Fujitsu Enterprise Postgres has a logging mechanism for events and error messages. Logs are essential if something goes wrong with your program or database. However, when DBAs or developers are attempting to remedy a problem, logs may cause misunderstanding. For this reason, it is crucial that you configure and handle your logs properly.
The importance of logging
First and foremost, logging enables the DBAs to diagnose and fix the issues. Simply put, the database log should be the first place to check, to identify or diagnose any issue. Errors, defects, security holes, and performance degradation are all examples of issues. The database log provides the overall activity tracking along with a timestamp, which is helpful to manage and operate the database for your application.
Logging has advantages such as:
- Issue diagnosis: Suppose you wish to simulate an issue that a user has reported in your development or production environment. If you have the issue recorded in a log file, it will be simpler for you to refer to those logs and duplicate the user action in a non-production or test environment for additional analysis.
- Analytics: We can learn more about application peak times, session or connection lifetimes, etc. by looking at database logs. You may further evaluate user activity and potentially enhance the user experience, application speed, and database performance by adding more data to the log files.
New logging format jsonlog
As we know, PostgreSQL supports several methods for logging server messages, including stderr, csvlog, and syslog - and on Windows, the eventlog. Version 15 now includes a new logging method called jsonlog.
The default value to log is stderr only. This parameter can only be set in postgresql.conf or on the server command line.
As I mentioned above, jsonlog is a new value that can be set to log_destination to provide the database logs in the JSON format. The particular format is very convenient for feeding the logs to other applications like monitoring tools. The files generated by this log format are suffixed with .json and use the same rotation policies as the other formats (stderr and csvlog), depending on the backend configuration.
When there’s more than one value set in the log_destination parameter, PostgreSQL will store a file named current_logfiles in the data directory, where each line of that file will represent the format and the current logfile name where PostgreSQL has to log the data.
[postgres@localhost PGSQL15]$ pwd
/opt/PGSQL15
[postgres@localhost PGSQL15]$ cat current_logfiles
stderr log/postgresql-2022-10-12_085848.log
jsonlog log/postgresql-2022-10-12_085848.json
DBAs, sysadmins, and developers can keep track of where the system is going to log with the current settings. This is useful when there is a log rotation in place, when troubleshooting issues, and when automatically feeding the current JSON format log to third-party applications for monitoring.
Conclusion
In this post, I have introduced some of the new features released in Fujitsu Enterprise Postgres 15. There are many more enhancements and changes in addition to the ones I've described here, which make Fujitsu Enterprise Postgres the enterprise Postgres offering of choice for multi/hybrid cloud environments.
My colleagues and I will be publishing technical deep-dive articles in the coming weeks. Make sure you sign up for notifications below.
leverages and extends the strength and reliability of PostgreSQL with additional enterprise features.
