In this example, you:
Perform the following steps to create a PostgreSQL table named forpxf_table1
in the public
schema of a database named pgtestdb
, and grant a user named pxfuser1
all privileges on this table:
Identify the host name and port of your PostgreSQL server.
Connect to the default PostgreSQL database as the postgres
user. For example, if your PostgreSQL server is running on the default port on the host named pserver
:
$ psql -U postgres -h pserver
Create a PostgreSQL database named pgtestdb
and connect to this database:
=# CREATE DATABASE pgtestdb;
=# \connect pgtestdb;
Create a table named forpxf_table1
and insert some data into this table:
=# CREATE TABLE forpxf_table1(id int);
=# INSERT INTO forpxf_table1 VALUES (1);
=# INSERT INTO forpxf_table1 VALUES (2);
=# INSERT INTO forpxf_table1 VALUES (3);
Create a PostgreSQL user named pxfuser1
:
=# CREATE USER pxfuser1 WITH PASSWORD 'changeme';
Assign user pxfuser1
all privileges on table forpxf_table1
, and exit the psql
subsystem:
=# GRANT ALL ON forpxf_table1 TO pxfuser1;
=# \q
With these privileges, pxfuser1
can read from and write to the forpxf_table1
table.
Update the PostgreSQL configuration to allow user pxfuser1
to access pgtestdb
from each Greenplum Database host. This configuration is specific to your PostgreSQL environment. You will update the /var/lib/pgsql/pg_hba.conf
file and then restart the PostgreSQL server.
You must create a JDBC server configuration for PostgreSQL and synchronize the PXF configuration. The PostgreSQL JAR file is bundled with PXF, so there is no need to manually download it.
This procedure will typically be performed by the Greenplum Database administrator.
Log in to the Greenplum Database coordinator host:
$ ssh gpadmin@<coordinator>
Create a JDBC server configuration for PostgreSQL as described in Example Configuration Procedure, naming the server directory pgsrvcfg
. The jdbc-site.xml
file contents should look similar to the following (substitute your PostgreSQL host system for pgserverhost
):
<?xml version="1.0" encoding="UTF-8"?>
Synchronize the PXF server configuration to the Greenplum Database cluster:
gpadmin@coordinator$ pxf cluster sync
Perform the following procedure to create a PXF external table that references the forpxf_table1
PostgreSQL table that you created in the previous section, and reads the data in the table:
Create the PXF external table specifying the jdbc
profile. For example:
gpadmin=# CREATE EXTERNAL TABLE pxf_tblfrompg(id int)
LOCATION ('pxf://public.forpxf_table1?PROFILE=jdbc&SERVER=pgsrvcfg')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
Display all rows of the pxf_tblfrompg
table:
gpadmin=# SELECT * FROM pxf_tblfrompg;
id
----
1
2
3
(3 rows)
Perform the following procedure to insert some data into the forpxf_table1
Postgres table and then read from the table. You must create a new external table for the write operation.
Create a writable PXF external table specifying the jdbc
profile. For example:
gpadmin=# CREATE WRITABLE EXTERNAL TABLE pxf_writeto_postgres(id int)
LOCATION ('pxf://public.forpxf_table1?PROFILE=jdbc&SERVER=pgsrvcfg')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
Insert some data into the pxf_writeto_postgres
table. For example:
=# INSERT INTO pxf_writeto_postgres VALUES (111);
=# INSERT INTO pxf_writeto_postgres VALUES (222);
=# INSERT INTO pxf_writeto_postgres VALUES (333);
Use the pxf_tblfrompg
readable external table that you created in the previous section to view the new data in the forpxf_table1
PostgreSQL table:
gpadmin=# SELECT * FROM pxf_tblfrompg ORDER BY id DESC;
id
-----
333
222
111
3
2
1
(6 rows)