Installation
PostgreSQL installation command on Fedora:
# yum -y install postgresql-server phpPgAdmin
It assumes that you have already Apache web server installed (package httpd)
Setup
PostgreSQL database needs to be initialized first.
# postgresql-setup initdb # su postgres bash-4.2$ createuser --superuser -P pgadmin bash-4.2$ createdb --encoding=utf-8 --owner=pgadmin pgadmin # systemctl start postgresql.service # systemctl enable postgresql.service
Also it is necessary to enable logging into phpPgAdmin:
# vim /etc/phpPgAdmin/config.inc.php $conf['extra_login_security'] = false;
To change the PostgreSQL password:
$ sudo -u postgres psql password pgadmin q
Setting up PostgreSQL database can be tricky. Just remember that after installation there is a user called postgres, who is a user in the operating system. The most comfortable way is to login as superuser and then as postgres user. After that you are able to create a database superuser, for example pgadmin. It’s necessary to create a database for him. This is not enough, you have to disable default authentication methods in /var/lib/pgsql/data/pg_hba.conf and change them to md5. After that and restarting the service I was able to login into phpPgAdmin as a pgadmin user.
# su postgres bash-4.2$ vim /var/lib/pgsql/data/pg_hba.conf local all all md5 host all all 127.0.0.1/32 md5 host all all ::1/128 md5 bash-4.2$ exit # systemctl restart postgresql.service
Creating users and their databases
The easiest way is to login to phpPgAdmin as pgadmin user. First you need to create a role, which seems to be the same as user in phpPgAdmin terminology. Fill in the role’s name, password, password confirmation and don’t forget to check at least “Can login?” check box. Then create a database for this user, for simplicity it is good habit to use the same name as the name of the user (role). The latest thing is to grant privileges for the selected user on already created database.
Running SQL commands from CLI
This example will focus on a simple Bash script, which is able to reset (drop and create) a PostgreSQL database. The script may look like this:
#!/bin/bash # PostgreSQL 9.1 pg_stat_activity.procpid, 9.2 has just pg_stat_activity.pid psql -U pgadmin -c "update pg_database set datallowconn = 'false' where datname = '$DATABASE'; SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE datname = '$DATABASE';" psql -U pgadmin -c "drop database $DATABASE;" psql -U pgadmin -c "create database $DATABASE owner $DATABASE encoding 'UTF8';"
Script assumes that you have already set up a super user pgadmin. Still there are few things to be done to run the script. The first thing is to edit the file pg_hba.conf and change authentication method to md5.
# vim /var/lib/pgsql/data/pg_hba.conf local all all md5 host all all 127.0.0.1/32 md5 host all all ::1/128 md5
Now you should be able to run the script, but for each command you would have to fill in the password. To remove this necessity, you can save the password in your home directory.
$ vim ~/.pgpass localhost:5432:*:pgadmin:password
Please note that this is not safe to save the password as a plain text, but I believe that for a local testing instance of PostgreSQL database it is a satisfactory solution.
Caveats
- phpPgAdmin is case sensitive, you can access it by http://localhost/phpPgAdmin
Hints
- This utility will remove orphaned large objects from the database, its diagnosis mode is:
/usr/pgsql-9.3/bin/vacuumlo -n -v database_name
