Step by Step Tutorial to Install and Configure PostgreSQL in OpenSUSE 12 Part2
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
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:~>
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:~ #
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 <username>
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 <username> 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.