PostgreSQL Support for Large Object Replication

Create a two-node pgEdge cluster on the localhost to demonstrate how pgEdge Platform replicates large objects, share a native PSQL example, and more.

Ahsan Hadi

Aug. 12, 24 · Tutorial

Likes (1)

Comment

Save

4.9K Views

Replication of large objects isn't currently supported by the community version of PostgreSQL logical replication. If you try to replicate a large object with logical replication, PostgreSQL will return: Large objects aren’t supported by logical replication. It's a meaningful error (always nice), but not helpful if you have large objects that you need to replicate.

pgEdge has developed an extension named LargeObjectLOgicalReplication (LOLOR) that provides support for replicating large objects. The primary goal of LOLOR is to provide seamless replication of large objects with pgEdge Spock multi-master distributed replication.

You can access and manipulate large objects in a PostgreSQL database with the following client interface functions:

    
  

   lo_create
lo_import
lo_import_with_oid
lo_open
lo_export
lo_read
lo_write
lo_seek
lo_seek64
  

The pgEdge LOLOR extension supports the same large objects functions put in place by PostgreSQL, so all of your existing applications that use the previously mentioned functions will continue to work seamlessly.

The easiest way to install the LOLOR extension is with the pgEdge Platform. After installing pgEdge Platform, you can use pgEdge Platform to install LOLOR, create the extension, and add it to the shared_preload_libraries parameter by navigating into the pgedge installation directory and running the command:

./pgedge install lolor

In this blog, we are going to create a two-node pgEdge cluster on the localhost to demonstrate how pgEdge Platform replicates large objects. We'll also share a native PSQL example of using the extension for replicating large objects, and a JDBC example that shows how we can use the extension from a Java program using a JDBC driver.

In any directory owned by your non-root user, use the following command to install pgEdge on all nodes of the cluster; you'll need to invoke this command on each replication node host:

    
   python3 -c "$(curl -fsSL https://pgedge-download.s3.amazonaws.com/REPO/install.py)"

Node 1 Setup

Navigate into the pgEdge directory on node 1 and perform the following steps :

Run the following command to set up the pgEdge platform. This command installs PostgreSQL version 16 and the pgEdge Spock and Snowflake extensions.

    
   ./pgedge setup -U demo -P pgedge110 -d testdb -p 5432

-U is the name of the database superuser
-P is the password for the database user
-d is the name of the database
-p is the port number (5432 is default PostgreSQL port)

Then, run the following command to create a Spock node (we are creating a node named n1). Note that the user named in the command below (in our command, pgedge) needs to be an OS user:

pgedge spock node-create n1 'host=localhost user=pgedge dbname=testdb' testdb

The next command creates the subscription between n1 and n2. You should run this command after completing the initial pgEdge setup on n2.

    
   pgedge spock sub-create sub_n1n2 ‘host=localhost port=5444 user=pgedge dbname=testdb’ testdb

Then, use the following command to install the LOLOR extension:

pgedge install lolor

Then, source your PostgreSQL installation, connect with PSQL, and run the CREATE EXTENSION statement to create the LOLOR extension:

    
   source pgedge/pg16/pg16.env
PSQL -U demo -d testdb -p 5432
CREATE EXTENSION lolor;

You'll also need to set the lolor.node configuration parameter before using the extension. Set the value to the number that corresponds to the node on which you're setting the parameter; the value can be from 1 to 2^28.

lolor.node=1

Please restart the server after adding the above configuration parameter to the postgresql.conf file. The postgresql.conf file is located in the data directory under your PostgreSQL installation.
Before using the LOLOR functionality, you also need to add the large object catalog tables to the default replication set. You can use the following commands:

    
   ./pgedge spock repset-add-table default 'lolor.pg_largeobject' testdb
./pgedge spock repset-add-table default 'lolor.pg_largeobject_metadata' testdb

The following commands are executed to enable automatic DDL replication :

    
   ./pgedge db guc-set spock.include_ddl_repset on
./pgedge db guc-set spock.allow_ddl_from_functions on
./pgedge db guc-set spock.include_ddl_repset on

Node 2 Setup

Navigate into the pgEdge directory on node 2 and perform the following steps to configure the LOLOR extension:

Run the following command to install pgEdge Platform. This will install PG-16, and the pgEdge Spock and Snowflake extensions.

    
   ./pgedge setup -U demo -P pgedge110 -d testdb -p 5444

-U is the database superuser
-P is the password for the database user
-d is the name of the database
-p is the port number (5444)

Use the following command to create a Spock node. Please note that the user provided in the following command needs to be an OS user :

    
   ./pgedge spock node-create n2 'host=localhost user=pgedge port=5444 dbname=testdb' testdb

Then, use the following command to create the subscription between n2 and n1:

    
   ./pgedge spock sub-create sub_n2n1 ‘host=localhost port=5432 user=pgedge dbname=testdb’ testdb

Now we are ready to install the LOLOR extension with the command:

./pgedge install lolor

Then, log in PSQL and invoke the CREATE EXTENSION statement:

    
   ./psql -U demo testdb -p 5444
CREATE EXTENSION lolor;

You must set lolor.node to a number that represents the node in the replication cluster before using LOLOR. Acceptable values range from 1 to 2^28.

lolor.node=2

Please restart the server after adding the above configuration parameter to postgresql.conf.
After setting the lolor.node parameter, use the following commands to add the large object catalog tables to the default replication set:

    
   ./pgedge spock repset-add-table default 'lolor.pg_largeobject' testdb
./pgedge spock repset-add-table default 'lolor.pg_largeobject_metadata' testdb

Then, execute the following commands to enable automatic DDL replication :

    
   ./pgedge db guc-set spock.enable_ddl_replication on
./pgedge db guc-set spock.allow_ddl_from_functions on
./pgedge db guc-set spock.include_ddl_repset on

Example: Using the PSQL Command Line to Exercise LOLOR

In the sections that follow, we are going to do a short test that demonstrates large object replication using the PSQL client. PSQL is a secure, native PostgreSQL client that uses the libpq driver to negotiate connections.

First, we are going to perform the following SQL commands on node 1:

    
   create table test_lolor(id int primary key, lo_object oid);

INSERT INTO test_lolor VALUES 
(008, lo_from_bytea(0, '\xaced0005774d0a060805100418005243080010001a3918002000320608011000180042121a080a044d41494e100022060a044d41494e52090a0744454641554c545a0c0a0a0805320608001000180122026800'));

testdb=# select id,lo_get(lo_object) from test_lolor; 
 id |                                                                                  lo_get                                                                 
                 
----+---------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------
  8 | \xaced0005774d0a060805100418005243080010001a3918002000320608011000180042121a080a044d41494e100022060a044d41494e52090a0744454641554c545a0c0a0a080532060800
1000180122026800
(1 row)

We have auto_ddl enabled so the table is also getting replicated to other nodes. We can query node 2 with the following SELECT statement to confirm that the large object was replicated:

    
   testdb=# select id,lo_get(lo_object) from test_lolor; 
 id |                                                                                  lo_get                                                                 
                 
----+---------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------
  8 | \xaced0005774d0a060805100418005243080010001a3918002000320608011000180042121a080a044d41494e100022060a044d41494e52090a0744454641554c545a0c0a0a080532060800
1000180122026800
(1 row)

Example: Using a JDBC Connection to Query a Large Object

The following program code connects with a pgEdge node and loads /etc/os-release file in the database as a large object and perform retrieval operations.

To simplify connection management, you can specify connection information in the app.properties file, and then reference the file in your JDBC connection.

app.properties:

    Java
   
 

   # JDBC
Jdbc.drivers=org.postgresql.Driver
url=jdbc:postgresql://localhost:5432/lolordb
username=asif
password=password




example.java

package lolor;

import java.sql.*;
import java.io.*;
import java.util.Properties;
import java.nio.charset.StandardCharsets;

import org.postgresql.PGConnection;
import org.postgresql.largeobject.LargeObject;
import org.postgresql.largeobject.LargeObjectManager;

public class Example {
	private static Connection pgconn = null;
	private static Properties dbProps;
	private final static String dbPropsFile = "app.properties";

	/*
 	* load property file
 	*/
	public static void loadDBPropertiesFile() throws Exception {

    	dbProps = new Properties();
    	InputStream in = new FileInputStream(dbPropsFile);
    	dbProps.load(in);
    	in.close();
	}

	/*
 	* Connect with PG
 	*/
	public static void connectPG()
        	throws Exception {
    	try {
        	// Set the search_path to pick lolor schema first
        	dbProps.setProperty("options", "-c search_path=lolor,\"$user\",public,pg_catalog");

        	pgconn = DriverManager.getConnection(dbProps.getProperty("url"), dbProps);
        	pgconn.setAutoCommit(false);
    	} catch (SQLException e) {
        	throw new RuntimeException(e);
    	}
	}

	/*
 	* Close the connection
 	*/
	public static void disconnectPG()
        	throws Exception {
    	try {
        	pgconn.close();
    	} catch (SQLException e) {
        	throw new RuntimeException(e);
    	}
	}

	/*
 	* Run query and return results
 	* Perform commit if asked
 	*/
	public static String executeSQL(String sql, boolean doCommit)
        	throws Exception {
    	String result = "";
    	try {
        	StringBuilder sbResult = new StringBuilder();
        	PreparedStatement ps = pgconn.prepareStatement(sql);
        	ResultSet rs = ps.executeQuery();
        	ResultSetMetaData rsmd = rs.getMetaData();
        	int columnsNumber = rsmd.getColumnCount();
        	for (int i = 1; i <= columnsNumber; i++) {
            	if (i > 1)
                	sbResult.append(",");
            	sbResult.append(rsmd.getColumnName(i));
        	}
        	sbResult.append("\n");
        	if (true) {
            	while (rs.next()) {
                	for (int i = 1; i <= columnsNumber; i++) {
                    	if (i > 1)
                        	sbResult.append(",");
                    	String columnValue = rs.getString(i);
                    	sbResult.append(columnValue);
                	}
                	sbResult.append("\n");
            	}
        	}
        	if (doCommit) {
            	pgconn.commit();
        	}
        	result = sbResult.toString();
        	return result;
    	} catch (SQLException e) {
        	// 02000 = no_data
        	if (e.getSQLState().compareTo("02000") == 0) {
            	return result;
        	} else {
            	pgconn.rollback();
            	throw new RuntimeException(e);
        	}
    	}
	}

	/*
 	* Initialize database
 	*/
	public static void initDB()
        	throws Exception {
            	executeSQL("DROP TABLE pglotest_blobs;", true);
            	String createTableSql = "CREATE TABLE pglotest_blobs (\n" +
            	"    	fname       	text PRIMARY KEY,\n" +
            	"    	blob        	oid\n" +
            	");";
            	executeSQL(createTableSql, true);
            	executeSQL("CREATE EXTENSION IF NOT EXISTS lolor;", true);
        	}

   /*
 	* Perform insert operation
 	* It internally calls lo_create, lo_open, lo_write, lo_close
 	* */
	static byte[] do_insert(String fname)
        	throws Exception {
    	File file;
    	FileInputStream fis;
    	LargeObjectManager lom;
    	long oid;
    	LargeObject lo;
    	byte[] buf = new byte[10];
    	int n;
    	ByteArrayOutputStream byteArrayOutStr = new ByteArrayOutputStream();

    	// Open the input file as InputStream
    	file = new File(fname);
    	fis = new FileInputStream(file);

    	// Create the LO
    	lom = ((PGConnection) pgconn).getLargeObjectAPI();
    	oid = lom.createLO();
    	lo = lom.open(oid, LargeObjectManager.WRITE);
    	while ((n = fis.read(buf, 0, buf.length)) > 0) {
        	lo.write(buf, 0, n);
        	byteArrayOutStr.write(buf, 0, n);
    	}
    	lo.close();

    	// Create the entry in the pglotest_blobs table
    	PreparedStatement ps = pgconn.prepareStatement("INSERT INTO pglotest_blobs VALUES (?, ?)");
    	ps.setString(1, fname);
    	ps.setLong(2, oid);
    	ps.execute();
    	ps.close();

    	// Close the input file and commit the transaction
    	fis.close();
    	pgconn.commit();
    	return byteArrayOutStr.toByteArray();
	}

	/*
 	* Perform read operation
 	* It internally calls lo_open, loread, lo_close
 	*/
	static byte[] do_select(String fname)
        	throws Exception {
    	LargeObjectManager lom;
    	long oid;
    	LargeObject lo;
    	byte[] buf = new byte[10];
    	int n;
    	ByteArrayOutputStream byteArrayOutStr = new ByteArrayOutputStream();

    	// Get the OID of the LO with that filename
    	PreparedStatement ps = pgconn.prepareStatement("SELECT blob FROM pglotest_blobs WHERE fname = ?");
    	ps.setString(1, fname);
    	ResultSet rs = ps.executeQuery();
    	if (rs.next()) {
        	// Open the LO and read its content
        	oid = rs.getLong(1);
        	lom = ((PGConnection) pgconn).getLargeObjectAPI();
        	lo = lom.open(oid, LargeObjectManager.READ);
        	while ((n = lo.read(buf, 0, buf.length)) > 0) {
            	byteArrayOutStr.write(buf, 0, n);
        	}
        	lo.close();
    	} else {
        	throw new Exception("Entry for " + fname + " not found");
    	}

    	// Rollback the transaction
    	pgconn.rollback();
    	return byteArrayOutStr.toByteArray();
	}

	public static void main(String[] args) throws Exception {
    	// Pick a sample file
    	String textFile1 = "/etc/os-release";

    	// Initialization
    	loadDBPropertiesFile();
    	connectPG();
    	initDB();

    	// Perform LO operations
    	byte[] bufInput = do_insert(textFile1);
    	byte[] bufRetrieved = do_select(textFile1);

    	// Verify the results
    	String input = new String(bufInput, StandardCharsets.UTF_8);
    	String retrieved = new String(bufRetrieved, StandardCharsets.UTF_8);

    	System.out.println("-----------------------");
    	System.out.println("Text Input: ");
    	System.out.println("-----------------------\n" + input);
    	System.out.println("-----------------------");
    	System.out.println("Text Retrieved: ");
    	System.out.println("-----------------------\n" + retrieved);
	}
}

  

Availability

LOLOR large object replication is now available as part of the pgEdge Platform.

For more information about installing and using the pgEdge Platform, visit the pgEdge site.

References

Java Database Connectivity Object (computer science) Replication (computing) PostgreSQL

Published at DZone with permission of Ahsan Hadi. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

Trending