PostgreSQL Configuration

PostgreSQL Configuration
Prev	Appendix A. Resource Managers Configuration

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.

Figure A.3. Deploy model of an example with PostgreSQL DBMS

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 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

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”.

Set-up PostgreSQL environment

Start-up the PostgreSQL server

If your server didn't start-up automatically at boot time, you could start it with the following commands:

[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

[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

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

[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 row like the following one:

[Shell terminal session]
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@ubuntu1204-64:~$ createdb testdb tiian@ubuntu1204-64:~$ psql testdb psql (9.1.24) Type "help" for help. testdb=> testdb=> CREATE TABLE "authors" ( testdb(> "id" integer NOT NULL, testdb(> "last_name" text, testdb(> "first_name" text, testdb(> Constraint "authors_pkey" Primary Key ("id")); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors" CREATE TABLE testdb=> select * from authors; id \| last_name \| first_name ----+-----------+------------ (0 rows)

[PostgreSQL terminal session]

tiian@ubuntu1204-64:~$ createdb testdb
tiian@ubuntu1204-64:~$ psql testdb
psql (9.1.24)
Type "help" for help.

testdb=>
testdb=> CREATE TABLE "authors" (
testdb(> "id" integer NOT NULL,
testdb(> "last_name" text,
testdb(> "first_name" text,
testdb(> Constraint "authors_pkey" Primary Key ("id"));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors"
CREATE TABLE

testdb=> select * from authors;
 id | last_name | first_name
----+-----------+------------
(0 rows)

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, CentOS and RHEL) 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.

Prev	Up
Oracle DMBS Configuration	Home