I would not go into details of what is PostgreSQL as I am pretty sure you already know about it. But in this guide, we will install and use PostgreSQL in Arch or Manjaro operating system with PHP including how to manage the databases.
So let’s get started.
We will install all the necessary packages first including PHP.
sudo pacman -S postgresql php php-pgsql
To make PHP work with PostgreSQL, we have to enable it in
sudo vim /etc/php/php.ini
Uncomment the following lines (line numbers are given as a guide for you).
extension=pdo_pgsql (line number 929) extension=pgsql (line number 931)
Restart Apache or your installed web server application.
sudo systemctl restart httpd
PostgreSQL initial setup
After installation we need to setup the database server so we can use it anywhere we would like to.
sudo systemctl start postgresql
On initial systemctl startup if you get an error related to
initdb: data folder does exist. Login to PostgreSQL as
sudo -iu postgres
Run the following command.
initdb --locale en_US.UTF-8 -D '/var/lib/postgres/data' exit
Now try to start it again.
sudo systemctl start postgresql sudo systemctl enable postgresql
You can also check the PostgreSQL clusters with.
Create users and databases
Login to PostgreSQL space as
sudo -iu postgres
Create a user with
createuser tool and
-P flag to set a password.
pguser is the username.
createuser pguser -P
Create a database with
my_test_db is the database name.
Once done, we will assign
Run the following command to grant all the privileges to user
GRANT ALL PRIVILEGES ON DATABASE my_test_db TO pguser;
This way pguser has access to my_test_db too.
Now if you want that
pguser should be able to create databases too (for example using a PHP script or any other tool). Run this command to alter
pguser and give permission of creating databases. Quit with
ALTER USER pguser CREATEDB; \q
Exit if you are done here.
Access from PHP
Now you can access PostgreSQL with your PHP scripts and you already have a database ready for use.
If you want to manipulate PostgreSQL databases/tables from a UI. I would recommend to install Adminer and login to PostgreSQL as
pguser which we created earlier.
You will be able to create databases too under this user as we granted it the permission.
To backup PostgreSQL databases, you can use
pg_dump -U pguser -W -F p my_test_db > /home/user/my_test_db_dump.sql
-U : PostgreSQL user
-W : force to ask for user password
-F : output file format
p : plain text file
-F can have values like,
p (plain text)
Check out help page of
pg_dump for more options and details.
You can dump all the databases with
To restore a database, use
psql -U pguser -W my_test_db < /home/user/my_test_db_dump.sql