Government | Commercial

Step by Step Tutorial to Install and Configure PostgreSQL in OpenSUSE 12 Part2

for Part1

On this part we will do a quick tuning of few mix memory and connectivity parameters that I know would help improve 20% to 30% performance for our Jira bug and issue tracking system:

# - Memory -
shared_buffers = 50MB
# - Planner Cost Constants -
effective_cache_size = 128MB
# - Connection Settings -
listen_addresses = '*'
port = 5432                             	# (change requires restart)
max_connections = 110

So let’s do a quick vi to /var/lib/pgsql/data/postgresql.conf and add the values as shown above or as appropriate to your environment then save the file with :wq! To exit as well.

Next as you can see some of these parameters require a restart so let’s do that

postgres@opsujira:~> pg_ctl -D /var/lib/pgsql/data restart

Alternatively:

postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres=#

Or 
postgres@opsujira:~> pg_ctl -D data reload
server signaled
postgres@opsujira:~>

Then let’s check the status:

postgres@opsujira:~> pg_ctl -D /var/lib/pgsql/data status
pg_ctl: server is running (PID: 25161)
/usr/bin/postgres "-D" "/var/lib/pgsql/data"
postgres@opsujira:~> 

Client Connection:

After a successful install and the quick fine tuning shown above, next is to test your PostgreSQL for client connections:

By default and for good security reason PostgreSQL is configured to allow local connections only and for postgres user with some limitations.

So we’ll need to do some adjustments to expand remote client connectivity using RSA and VPN security Tunneling. Let’s revisit the /var/lib/pgsql/data/pg_hba.conf file and add a line as follow at the end of the file:

…
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident
host     postgres        postgres        192.168.11.11    255.255.255.0  trust
…
opsujira:~ #

Restart postgreSQL
Once done,restart postgreSQL as follows:

opsujira:~ # rcpostgresql restart

If you require some a bit more tighten security with encryption then add this line instead

host       all            all              192.168.11.11      255.255.255.0    md5

Create and Delete users
To create an user

opsujira:~ # su postgres
postgresql@opsujira:~> createuser -D 

or

postgres@opsujira:~> psql postgres
psql (9.1.3)
Type "help" for help.
postgres=# CREATE USER alfb WITH PASSWORD 'myPassword';
CREATE ROLE
postgres=#

To delete a user

postgresql@opsujira:~> dropuser 
postgres=# q

Next we’ll go over Configuring the Client PgAdminIII on OpenSUSE and PgAdmin3 on my Mac for remote connectivity via RSA and secure Tunnel VPN.