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.
·
Join the DZone community and get the full member experience.
Join For FreeReplication 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_seek64The 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
n1andn2. You should run this command after completing the initial pgEdge setup onn2.
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 EXTENSIONstatement 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.nodeconfiguration 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 onNode 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
n2andn1:
./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 EXTENSIONstatement:
./psql -U demo testdb -p 5444
CREATE EXTENSION lolor;
- You must set
lolor.nodeto 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.nodeparameter, 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:
# 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
Published at DZone with permission of Ahsan Hadi. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments