The following diagram represents a simplified vision of the components
necessary to run an example program (example5_mys
in the picture) that uses PostgreSQL as an XA Resource Manager.
This section has been developed using PostgreSQL 9.1.24 (and upper) for Linux. Here is a brief list of the tested versions for Ubuntu 12.04, 14.04, 16.04, 18.04 and CentOS/RHEL 7.3, CentOS 8 and the installed packages:
tiian@ubuntu1204-64:/tmp$ dpkg -l | grep -i -e pq -e postgresql
ii libpq-dev 9.1.24-0ubuntu0.12.04 header files for libpq5 (PostgreSQL library)
ii libpq5 9.1.24-0ubuntu0.12.04 PostgreSQL C client library
ii postgresql 9.1+129ubuntu1 object-relational SQL database (supported version)
ii postgresql-9.1 9.1.24-0ubuntu0.12.04 object-relational SQL database, version 9.1 server
ii postgresql-client-9.1 9.1.24-0ubuntu0.12.04 front-end programs for PostgreSQL 9.1
ii postgresql-client-common 129ubuntu1 manager for multiple PostgreSQL client versions
ii postgresql-common 129ubuntu1 PostgreSQL database-cluster manager
tiian@ubuntu1404-64:/tmp$ dpkg -l | grep -i -e pq -e postgresql
ii libpq-dev 9.3.16-0ubuntu0.14.04 amd64 header files for libpq5 (PostgreSQL library)
ii libpq5 9.3.16-0ubuntu0.14.04 amd64 PostgreSQL C client library
ii postgresql 9.3+154ubuntu1 all object-relational SQL database (supported version)
ii postgresql-9.3 9.3.16-0ubuntu0.14.04 amd64 object-relational SQL database, version 9.3 server
ii postgresql-client-9.3 9.3.16-0ubuntu0.14.04 amd64 front-end programs for PostgreSQL 9.3
ii postgresql-client-common 154ubuntu1 all manager for multiple PostgreSQL client versions
ii postgresql-common 154ubuntu1 all PostgreSQL database-cluster manager
tiian@ubuntu1604:~$ dpkg -l | grep -i -e pq -e postgresql
ii libpq-dev 9.5.6-0ubuntu0.16.04 amd64 header files for libpq5 (PostgreSQL library)
ii libpq5:amd64 9.5.6-0ubuntu0.16.04 amd64 PostgreSQL C client library
ii postgresql 9.5+173 all object-relational SQL database (supported version)
ii postgresql-9.5 9.5.6-0ubuntu0.16.04 amd64 object-relational SQL database, version 9.5 server
ii postgresql-client-9.5 9.5.6-0ubuntu0.16.04 amd64 front-end programs for PostgreSQL 9.5
ii postgresql-client-common 173 all manager for multiple PostgreSQL client versions
ii postgresql-common 173 all PostgreSQL database-cluster manager
ii postgresql-contrib-9.5 9.5.6-0ubuntu0.16.04 amd64 additional facilities for PostgreSQL
tiian@ubuntu1804:~$ dpkg -l | grep -i -e pq -e postgresql
ii libpq-dev 10.3-1 amd64 header files for libpq5 (PostgreSQL library)
ii libpq5:amd64 10.3-1 amd64 PostgreSQL C client library
ii postgresql 10+190 all object-relational SQL database (supported version)
ii postgresql-10 10.3-1 amd64 object-relational SQL database, version 10 server
ii postgresql-client-10 10.3-1 amd64 front-end programs for PostgreSQL 10
ii postgresql-client-common 190 all manager for multiple PostgreSQL client versions
ii postgresql-common 190 all PostgreSQL database-cluster manager
[tiian@centos71-64 tmp]$ rpm -qa | grep -i -e pq -e postgresql
postgresql-libs-9.2.18-1.el7.x86_64
postgresql-devel-9.2.18-1.el7.x86_64
postgresql-9.2.18-1.el7.x86_64
postgresql-server-9.2.18-1.el7.x86_64
[tiian@rhel73 tmp]$ rpm -qa | grep -i -e pq -e postgresql
postgresql-devel-9.2.18-1.el7.x86_64
postgresql-9.2.18-1.el7.x86_64
postgresql-server-9.2.18-1.el7.x86_64
postgresql-libs-9.2.18-1.el7.x86_64
[tiian@centos8 tmp]$ rpm -qa | grep -i -e pq -e postgresql
postgresql-server-10.14-1.module_el8.2.0+487+53cc39ce.x86_64
libpq-12.4-1.el8_2.x86_64
postgresql-jdbc-42.2.3-3.el8_2.noarch
libpq-devel-12.4-1.el8_2.x86_64
postgresql-10.14-1.module_el8.2.0+487+53cc39ce.x86_64
If you were using a different version you would need to adapt some commands to your environment.
Note
If you did not yet installed the software, please refer to the official site for your Linux distribution or to the official site of PostgreSQL if your operating system does not distribute the software or you want to use a different PostgreSQL version. This manual does not give you information related to PostgreSQL: it is assumed that you have already installed and configured the database.
Note
This example requires you are running the database and the application on the same host: this is not a technical limitation, but a way to make it easy. Client/server configuration must work as well, but it needs some PostgreSQL extra configuration: please refer to the database documentation.
Important
The LIXA software must be configured to support the PostgreSQL server resource manager as explained in the section called “Linking third party resource managers”.
If your server didn't start-up automatically at boot time, you could start it with the following commands for Ubuntu:
| [Shell terminal session] |
tiian@ubuntu1204-64:~$ sudo service postgresql status Running clusters: tiian@ubuntu1204-64:~$ ps -ef|grep postgres|grep -v grep tiian@ubuntu1204-64:~$ sudo service postgresql start * Starting PostgreSQL 9.1 database server [ OK ] tiian@ubuntu1204-64:~$ sudo service postgresql status Running clusters: 9.1/main tiian@ubuntu1204-64:~$ ps -ef|grep postgres|grep -v grep postgres 1829 1 1 23:00 ? 00:00:00 /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf postgres 1831 1829 0 23:00 ? 00:00:00 postgres: writer process postgres 1832 1829 0 23:00 ? 00:00:00 postgres: wal writer process postgres 1833 1829 0 23:00 ? 00:00:00 postgres: autovacuum launcher process postgres 1834 1829 0 23:00 ? 00:00:00 postgres: stats collector process |
or with the following ones for CentOS:
| [Shell terminal session] |
[tiian@centos8 tmp]$ sudo /usr/bin/postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[tiian@centos8 tmp]$ sudo systemctl start postgresql.service
[tiian@centos8 tmp]$ sudo systemctl enable postgresql.service
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
[tiian@centos8 tmp]$ ps -ef|grep postgres|grep -v grep
postgres 2254 1 0 15:23 ? 00:00:00 /usr/bin/postmaster -D /var/lib/pgsql/data
postgres 2256 2254 0 15:23 ? 00:00:00 postgres: logger process
postgres 2258 2254 0 15:23 ? 00:00:00 postgres: checkpointer process
postgres 2259 2254 0 15:23 ? 00:00:00 postgres: writer process
postgres 2260 2254 0 15:23 ? 00:00:00 postgres: wal writer process
postgres 2261 2254 0 15:23 ? 00:00:00 postgres: autovacuum launcher process
postgres 2262 2254 0 15:23 ? 00:00:00 postgres: stats collector process
postgres 2263 2254 0 15:23 ? 00:00:00 postgres: bgworker: logical replication launcher
[tiian@centos8 tmp]$ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2020-10-11 15:23:45 CEST; 3min 7s ago
Main PID: 2254 (postmaster)
Tasks: 8 (limit: 11477)
Memory: 16.3M
CGroup: /system.slice/postgresql.service
├─2254 /usr/bin/postmaster -D /var/lib/pgsql/data
├─2256 postgres: logger process
├─2258 postgres: checkpointer process
├─2259 postgres: writer process
├─2260 postgres: wal writer process
├─2261 postgres: autovacuum launcher process
├─2262 postgres: stats collector process
└─2263 postgres: bgworker: logical replication launcher
Oct 11 15:23:44 centos8 systemd[1]: Starting PostgreSQL database server...
Oct 11 15:23:44 centos8 postmaster[2254]: 2020-10-11 15:23:44.877 CEST [2254] LOG: listening on IPv6 address "::1", port 5432
Oct 11 15:23:44 centos8 postmaster[2254]: 2020-10-11 15:23:44.877 CEST [2254] LOG: listening on IPv4 address "127.0.0.1", port 5432
Oct 11 15:23:44 centos8 postmaster[2254]: 2020-10-11 15:23:44.893 CEST [2254] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Oct 11 15:23:44 centos8 postmaster[2254]: 2020-10-11 15:23:44.935 CEST [2254] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Oct 11 15:23:44 centos8 postmaster[2254]: 2020-10-11 15:23:44.957 CEST [2254] LOG: redirecting log output to logging collector process
Oct 11 15:23:44 centos8 postmaster[2254]: 2020-10-11 15:23:44.957 CEST [2254] HINT: Future log output will appear in directory "log".
Oct 11 15:23:45 centos8 systemd[1]: Started PostgreSQL database server.
|
Switch to user postgres,
associate your user to a matching database user
[62]
; my personal account is
tiian
and I created the same user inside PostgreSQL database:
| [Shell terminal session] |
tiian@ubuntu1204-64:~$ sudo su - postgres [sudo] password for tiian: postgres@ubuntu1204-64:~$ createuser --createdb tiian Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n postgres@ubuntu1204-64:~$ exit logout |
Even if most of the examples use local connection, for JDBC it's
necessary even a host connection; verify that config file
pg_hba.conf contains a couple of rows like the
following ones:
| [Shell terminal session] |
host all all 127.0.0.1/32 md5 host all all ::1/128 md5 |
and assign your user a password inside PostgreSQL database:
| [Shell terminal session] |
tiian@ubuntu1404-64:~/lixa$ sudo su - postgres postgres@ubuntu1404-64:~$ psql psql (9.3.24) Type "help" for help. postgres=# ALTER USER tiian WITH PASSWORD 'passw0rd'; ALTER ROLE postgres=# \q |
Create a new database and a table necessary to store some data:
| [PostgreSQL terminal session] |
[tiian@centos8 tmp]$ createdb testdb
[tiian@centos8 tmp]$ psql testdb
psql (10.14)
Type "help" for help.
testdb=> CREATE TABLE "authors" ("id" integer NOT NULL,"last_name" text,"first_name" text,Constraint "authors_pkey" Primary Key ("id"));
CREATE TABLE
testdb=> select * from authors;
id | last_name | first_name
----+-----------+------------
(0 rows)
testdb=> \q
|
OK, the “authors” table was created. If something went wrong, you should refer to PostgreSQL documentation to fix the issue before the next step because you would not be able to execute the sample program without a basic running installation.
Change the
max_prepared_transactions
parameter in file
postgresql.conf
to allow the desired number of prepared transactions (i.e. 10):
shared_buffers = 24MB # min 128kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
max_prepared_transactions = 10 # zero disables the feature
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB # min 100kB
and restart the PostgreSQL server with something like
service postgresql restart
(Ubuntu) or
systemctl restart postgresql.service
(CentOS)
using
root user.
[62]
If you wanted to use a database user different than your own
UNIX user, as it ever happens when the database is hosted on
a different system, you should configure
pg_hba.conf as well. Look at the PostgreSQL
documentation to pick up all the necessary details.