User Tools

Site Tools


postgresql

PostgreSQL 9.6 Installation

1.0 --- Update packages, install PostgreSQL 9.6

sudo apt-get update

Install the PostgreSQL server and PostgreSQL contrib package which provides additional features for the PostgreSQL database:

sudo apt install postgresql postgresql-contrib

To check an verify that PostgreSQL server/client are installed, you can use the following command:

psql --version

1.1 --- Manage PostgreSQL on Debian 9

sudo systemctl start postgresql.service
sudo systemctl enable postgresql.service
sudo systemctl status postgresql.service

PostgrSQL status should look like this:

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
   Active: active (exited) since Fri 2019-01-25 10:15:52 +06; 1s ago
  Process: 10493 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 10493 (code=exited, status=0/SUCCESS)

Jan 25 10:15:52 rpi systemd[1]: Starting PostgreSQL RDBMS...
Jan 25 10:15:52 rpi systemd[1]: Started PostgreSQL RDBMS.

1.2 --- Configure Postgres User

PostgreSQL uses role for user authentication and authorization, it just like Unix-Style permissions. By default, PostgreSQL creates a new user called “postgres” for basic authentication. To use PostgreSQL, you need to login to the “postgres” account, you can do that by typing:

sudo su - postgres
psql

And then change the password for postgres role by typing:

\password postgres

Then enter \q to leave the psql command line:

\q
exit

You can also use the sudo command to access the PostgreSQL prompt without switching users:

sudo -u postgres psql
\q

1.3 --- Creating Postgres Role and Database

Create a new PostgreSQL Role named pi:

sudo su - postgres -c "createuser pi"

Create a new PostgreSQL Database named pidb:

sudo su - postgres -c "createdb pidb"

To grant permissions to the pi user on the pidb we created in the previous step, connect to the PostgreSQL shell, and run the following query:

sudo -u postgres psql
grant all privileges on database pidb to pi;
\q

1.4 --- PostgreSQL config file paths

/etc/postgresql/9.6/main/postgresql.conf
/etc/postgresql/9.6/main/pg_hba.conf

2.0 --- Install phppgAdmin

sudo apt-get install phppgadmin php7.2-pgsql

Note: for buster release you may have to install like this: sudo apt-get install -t buster phppgadmin php7.2-pgsql

2.1 --- Configure Apache

Edit the /etc/apache2/conf-available/phppgadmin.conf configuration file by typing:

sudo nano /etc/apache2/conf-available/phppgadmin.conf

Comment out the line #Require local and add below the line Require all granted so that you can access from your browser:

[...]
# Only allow connections from localhost:
#Require local
Require all granted
[...]

2.2 --- Configure phppgAdmin

Edit the /etc/phppgadmin/config.inc.php configuration file by typing:

sudo nano /etc/phppgadmin/config.inc.php

Find the line '$conf['extra_login_security'] = true;' and change the value to 'false' so you can login to phpPgAdmin with user postgres:

[...]
$conf['extra_login_security'] = false;
[...]

Finaly we enable the configurations and restart services:

sudo a2enconf phppgadmin.conf
sudo systemctl restart postgresql
sudo systemctl restart apache2

You can now access phpPgAdmin with your browser https://rpi.tiger-park.com/phppgadmin.

x.x --- References

postgresql.txt · Last modified: 2019/01/25 13:35 by pi