Topic: PostgreSQL 9.2

[guest22]

Hi All,

the scl PostgreSQL how-to http://wiki.contribs.org/Software_Collections:PostgreSQL is about to be completed. Tests have shown PostgreSQL installs complete and is functional.

However, I do not have PostgreSQL experience on using the CLI and creating databases and users.

Would anybody with PostgreSQL experience please test the how-to and report back if CLI and functionality is working as one would expect from a 'normal' PostgreSQL server?

1. Does the how-to work for you to get PostgreSQL installed on your SME Server?
2. Does PostgreSQL on SME Server act/perform as a normal PostgreSQL server?

TIA
guest

[michelandre]

Hi all,

Nice work Hf.

In http://wiki.contribs.org/Software_Collections:PostgreSQL
initdb works fine:

# /etc/rc.d/init.d/postgresql92-postgresql initdb
Initialisation de la base de données :                     [  OK  ]
#

I received an error starting PosgreSQL

# /etc/rc.d/init.d/postgresql92-postgresql start
touch: impossible de faire un touch « /opt/rh/postgresql92/root/var/lock/subsys/postgresql92-postgresql »: Aucun fichier ou dossier de ce type (No file or directory of this type)
/etc/rc.d/init.d/postgresql92-postgresql: line 134: /opt/rh/postgresql92/root/var/run/postmaster.5432.pid: Aucun fichier ou dossier de ce type (No file or directory of this type)
#

I created both directories and files

# /etc/rc.d/init.d/postgresql92-postgresql start
Démarrage du service postgresql92-postgresql :             [  OK  ]
#

# /etc/rc.d/init.d/postgresql92-postgresql status
postmaster (pid  2441) en cours d'exécution...  (Running...)
#

Switching to user postgres

# su - postgres
-bash-4.1$

-bash-4.1$ whoami
postgres
-bash-4.1$

Run psql

-bash-4.1$ psql
-bash: psql : commande introuvable (command not found)
-bash-4.1$

-bash-4.1$ locate psql
/opt/rh/postgresql92/root/usr/bin/psql
-bash-4.1$


-bash-4.1$ /opt/rh/postgresql92/root/usr/bin/psql
/opt/rh/postgresql92/root/usr/bin/psql: error while loading shared libraries: libpq.so.postgresql92-5: cannot open shared object file: No such file or directory
-bash-4.1$

The file is there.

# locate libpq.so
/opt/rh/postgresql92/root/usr/lib64/libpq.so.postgresql92-5
/opt/rh/postgresql92/root/usr/lib64/libpq.so.postgresql92-5.5
#

According to: http://www.postgresql.org/docs/9.1/static/install-post.html

LD_LIBRARY_PATH=/opt/rh/postgresql92/root/usr/lib64 ; export LD_LIBRARY_PATH

Example taken from: http://www.micronator.org/?page_id=1765
Reference: https://wiki.postgresql.org/wiki/First_steps

Switch to user postgres

[root@sme9-docker /]# su - postgres
-bash-4.1$ whoami
postgres
-bash-4.1$

Set library path

-bash-4.1$ LD_LIBRARY_PATH=/opt/rh/postgresql92/root/usr/lib64 ; export LD_LIBRARY_PATH
-bash-4.1$
-bash-4.1$
-bash-4.1$ echo $LD_LIBRARY_PATH
/opt/rh/postgresql92/root/usr/lib64
-bash-4.1$

Run psql

-bash-4.1$ /opt/rh/postgresql92/root/usr/bin/psql
psql (9.2.7)
postgres=# Saisissez « help » pour l'aide.

postgres=#

Help

postgres=# help
Vous utilisez psql, l'interface en ligne de commande de PostgreSQL.
Saisissez:
    \copyright pour les termes de distribution
    \h pour l'aide-mémoire des commandes SQL
    \? pour l'aide-mémoire des commandes psql
    \g ou point-virgule en fin d'instruction pour exécuter la requête
    \q pour quitter
postgres=#

SCHEMA

postgres=# CREATE SCHEMA test;
CREATE SCHEMA
postgres=#

Create DB user

postgres=# CREATE USER michelandre PASSWORD 'toto';
CREATE ROLE
postgres=#

Grant

postgres=# GRANT ALL ON SCHEMA test TO michelandre;
GRANT
postgres=#

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO michelandre;
GRANT
postgres=#

Quit

postgres=# \q
-bash-4.1$

Switch back to root

-bash-4.1$ exit
logout
[root@sme9-docker /]#

Add a standard user to linux

[root@sme9-docker /]# adduser michelandre           \
> --home /home/e-smith/files/users/michelandre  \
> --groups shared                               \
> --shell /bin/bash
[root@sme9-docker /]#

Give password to new standard linux user

[root@sme9-docker /]# passwd michelandre
Changement de mot de passe pour l'utilisateur michelandre.
Entrez le nouveau mot de passe UNIX :
Retapez le nouveau mot de passe UNIX :
passwd : mise à jour réussie de tous les jetons d'authentification.
[root@sme9-docker /]#

Verify

[root@sme9-docker /]#cat /etc/passwd | grep michelandre
michelandre:x:3000:3000::/home/e-smith/files/users/michelandre:/bin/bash
[root@sme9-docker /]#

[root@sme9-docker /]#cat /etc/group | grep michelandre
shared:x:500:www,admin,public,michelandre
michelandre:x:3000:
[root@sme9-docker /]#

Switch to standard linux user

[root@sme9-docker /]# su - michelandre
[michelandre@sme9-docker ~]$

Set library path

[michelandre@sme9-docker ~]$ LD_LIBRARY_PATH=/opt/rh/postgresql92/root/usr/lib64 ; export LD_LIBRARY_PATH
[michelandre@sme9-docker ~]$

Verify

[michelandre@sme9-docker ~]$ echo $LD_LIBRARY_PATH
/opt/rh/postgresql92/root/usr/lib64
[michelandre@sme9-docker ~]$

Enter PostgreSQL

[michelandre@sme9-docker ~]$ /opt/rh/postgresql92/root/usr/bin/psql -d postgres
psql (9.2.7)
Saisissez « help » pour l'aide.

postgres=>

help

postgres=> help
Vous utilisez psql, l'interface en ligne de commande de PostgreSQL.
Saisissez:
    \copyright pour les termes de distribution
    \h pour l'aide-mémoire des commandes SQL
    \? pour l'aide-mémoire des commandes psql
    \g ou point-virgule en fin d'instruction pour exécuter la requête
    \q pour quitter
postgres=>

Create table

postgres=> CREATE TABLE test.test (coltest varchar(20));
CREATE TABLE
postgres=>

Insert

postgres=> insert into test.test (coltest) values ('It works!');
INSERT 0 1
postgres=>

Select

postgres=> SELECT * from test.test;
  coltest
-----------
 It works!
(1 ligne)

postgres=>

Drop

postgres=> DROP TABLE test.test;
DROP TABLE
postgres=>

Exit

postgres=> \q
[michelandre@sme9-docker ~]$

- Maybe make the psql link in /bin and permanent the path to the sql library.

Again, very nice work Hf

Michel-André

[guest22]

Thanks Michel-Andre for testing.

the 2 errors when trying to start PostgreSQL (missing directories and files) I had them too and also created them manually. I *think* it's a bug in the scl-postgresql92 package itself.

regarding psql: command not found. Did you follow this part:

http://wiki.contribs.org/Software_Collections:PostgreSQL#Enable_the_postgresql92_environment_at_boot_time

and ran the script manually to establish the environment variables or rebooted first?

Thanks again.
guest

[guest22]

I've adjusted the how-to based on your feedback.

[michelandre]

Hi Hf,

Sorry about the end of you contrib.

You are absolutely right. I did the rest of the procedure and all is working fine.

My apologies for not reading to the end,

Michel-André

[guest22]

No worries and thanks again. Maybe you are interested in scl-python too That's also 'done'.

[michelandre]

Hi again Hf,

Yes, it will be a good thing to install those php and python versions since they are much newer than the ones I installed.

When I installed PostgreSQL, I read that it was possible to store the data in a different directory than the standard one. I didn't know how to do that and since it is very easy to do a backup of DBs in Odoo I didn't try very hard to modify the standard directory.

Looking on how you did that, it looks like you just have to create a file and put the location of the data directory inside the created file. Is that the way? I also looked at the config file, quite a lot of parameters...

mkdir -p /opt/rh/postgresql92/root/etc/sysconfig/pgsql
echo PGDATA=/home/e-smith/files/pgsql/data > /opt/rh/postgresql92/root/etc/sysconfig/pgsql/postgresql92-postgres

I looked a little bit further and I can not see /home/e-smith/files/pgsql/data. I verified in /opt/rh/postgresql92 and I cannot see any data directory. I enter PostgreSQL and did a small DB to see where it will put it but I noticed no difference in either directory. My PostgreSQL knowledge is limited so I do not know what is going on.

I asked my knows it all friend Google...

[root@sme9-docker pgsql]# su - postgres
-bash-4.1$ psql
psql (9.2.7)
Saisissez « help » pour l'aide.

postgres=# show data_directory;
                data_directory
----------------------------------------------
 /opt/rh/postgresql92/root/var/lib/pgsql/data
(1 ligne)

Is it possible to install collections in /home/e-smith/files, it wil make it easier to upgrade to a newer SME as I am thinking of installing php and python collections also?

Collections are really a + for SME,

Michel-André

[guest22]

Looking on how you did that, it looks like you just have to create a file and put the location of the data directory inside the created file. Is that the way?

yes, that is the preferred RedHat way. As described here:http://wiki.contribs.org/Software_Collections:PostgreSQL#Database_location

Please note that you have to set this location _before_ you perform a initdb and you _have_ to restart postgreSQL so postgreSQL is aware of these new parameters, for they are read during the startup script. Also you have to restart postgreSQL for it to pick up on the new parameters. Don;t worry, a one time only thing to do.

So:
1. Stop postgreSQL
2. Set database location
3. Start postgreSQL
4. Perform initdb (Data will be written to /home/e-smith/files/pgsql/data)

From here on, after every reboot, /home/e-smith/files/pgsql/data is taken as the default data location.

Can you try again please?
Thanks,

guest