Accueil‎ > ‎

Suse 11.3 : PostGreSQL Installation

posted 4 Apr 2019, 07:35 by Christophe Noël   [ updated 5 Apr 2019, 03:00 ]
Here is how to install, configure and play with your PostGreSQL server on a SUSE 11 SP3 machine.

Installation

Zypper installs an old version (8.4), so if you wish to get a newer server, I guess you must update your packages and repositories. In my case, this is forbidden by the customer requirements.

The installation is therefore quite simple :
dcIIDBServer:~/CCN # zypper install postgresql-server
Loading repository data...
Warning: Repository 'SUSEupdate' appears to outdated. Consider using a different mirror or server.
Reading installed packages...
Resolving package dependencies...

The following NEW packages are going to be installed:
  libopenssl1_0_0 libreadline6 postgresql postgresql-libs postgresql-server

The following packages are not supported by their vendor:
  libopenssl1_0_0 libreadline6 postgresql postgresql-libs postgresql-server

5 new packages to install.
Overall download size: 4.6 MiB. After the operation, additional 21.0 MiB will be used.
Continue? [y/n/?] (y): y

Initialize postgres (cluster) data. It will create a database clusters, i.e. creating the directories in which the database data will live, generating the shared catalog tables (that belong to the whole cluster, rather than a single database).
mkdir /xxx/yyy/pgdata
chown postgres:postgres /xxx/yyy/pgdata
su - postgres
initdb -D /xxx/yyy/pgdata

Start (typically as root)
service postgresql start

Connect to server (a perform some checks)\d:
dcIIDBServer:~/CCN # su - postgres
postgres@dcIIDBServer:~> psql
psql (8.4.7)
Type "help" for help.
postgres=# \password postgres
Enter new password:
Enter it again:
SELECT current_database();
 current_database
------------------
 postgres
postgres=# \db
                   List of tablespaces
     Name     |    Owner     |         Location
--------------+--------------+---------------------------
 pg_default   | postgres     |
 pg_global    | postgres     |
 ssa_srv_data | ssa_srv_data | /data1/pgsql/ssa_srv_data
 test1        | ccntest      | /data1/pgsql/data
(4 rows)
postgres=# \dn
        List of schemas
        Name        |  Owner
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 public             | postgres

Remote Access Configuration

Configure remote access (allow):

vi /var/lib/pgsql/data/postgresql.conf

Edit as follows:
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
Take care that max_connections is set to 100 by default.

Authentication Enabling

By default PostgreSQL uses IDENT-based authentication and this will never allow you to login via -U and -W options. Allow username and password based authentication from your application by appling 'trust' as the authentication method for the JIRA database user. You can do this by modifying the pg_hba.conf file.

You can identify the location of the pg_hba.conf file by running the following command in psql command line, you'll need to be logged in as a superuser in the database:

postgres=# show hba_file ;
 hba_file
--------------------------------------
 /etc/postgresql/9.3/main/pg_hba.conf
(1 row)
 
This file controls:

Which hosts are allowed to connect
How clients are authenticated
Which PostgreSQL user names they can use
Which databases they can access

Edit the PostgreSQL Client Authorisation File /var/lib/pgsql/data/pg_hba.conf and change the authentication method used to md5. If needed, change 127.0.0.1/32 to all IPs mask (0.0.0.0/0)

local    all     all                      md5
host     all     all      0.0.0.0/0     md5
host     all     all      ::1/128          md5

Add optionally a line for local connection for postgres user

local all postgres trust     

General Advices

Always use quote when naming object in SQL queries (otherwise, the case is not preserved).

Connection

psql -d dbname -h hostname -p port -U username -W password

Check current user, database, etc.

SELECT current_user; (without parenthesis)
SELECT current_database();
\l (list databases)

User Creation

To create a new a user, we must login as postgres user, and then create a new role and database with the command below:

postgres@dcIIDBServer:~> psql
postgres=# CREATE USER myuser WITH ENCRYPTED PASSWORD 'myuser';

Modify password:
postgres=# alter user ccntest with encrypted password 'ccntest';

Grant privileges:
postgres=# grant all privileges on database ccndb to ccntest;

For creating users, note that there is also a command (in such case, use lowercase for the username):
createuser -s myuser

Tablespace Creation

Tablespaces in PostgreSQL allow  to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.

Note in the examples, we assume there is a directory owned by postgres at the following location: /data1/pgsql.
chown -R postgres:postgres /data1/pgsql/
Create directory for hosting the tablespace, e.g.:
su - postgres
mkdir /data1/pgsql/mytablespace

Connect to postgres (psql using postgres user) and create tablespace (owner must exists):
postgres@dcIIDBServer:~> psql ccndb
psql (8.4.7)
Type "help" for help.
ccndb=# CREATE TABLESPACE test1 OWNER test1 LOCATION '/data1/pgsql/data';
ERROR:  role "test1" does not exist
ccndb=# CRCREATEATE TABLESPACE test1 OWNER ccntest LOCATION '/data1/pgsql/data';
Note that size of table space is not limited

Mention a tablespace (if not the default):
CREATE TABLE foo(i int) TABLESPACE espace1;

Change to default:
SET default_tablespace = espace1;

List tablespaces:
SELECT spcname FROM pg_tablespace;

Infos: https://docs.postgresql.fr/8.1/manage-ag-tablespaces.html 

Database Creation

Create a database for a given tablespace (so it will become the default tablespace), or use the default tablespace.
CREATE DATABASE "ssa" [WITH TABLESPACE "xxx"];

Drop and Delete Objects

Note you should use quote to ensure the case is respected.
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 SSA_TEST  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

DROP TABLESPACE "MYTABLESPACE";
DROP DATABASE "MYDB";



Comments