PostgreSQL also known as Postgres is an open-source object-relational database used in various applications. Its strong flexibility, scalability, and robustness make it popular among other database engines. In this article, we cover how to install PostgreSQL on a centos machine. Note that these steps apply to all other Linux distributions.
Prerequisites:
- A Linux server running on Centos (preferably version 7 and higher)
- Server access with the root privileges
Follow the following steps to install PostgreSQL on centos
- System update:- Access the server on SSH and update the system package repository
sudo yum update- Install PostgreSQL
sudo yum install postgresql-server postgresql-contrib- Initialize the database:- Once PostgreSQL is installed, start it, and enable it to start on boot/reboot.
sudo postgresql-setup initdb #initialize database
sudo systemctl start postgresql #start database
sudo systemctl enable postgresql #set database to start on boot or reboot- Set up a password for PostgreSQL default User
Note that the default user is used to initiate the creation of other database users and other databases. To set a password for the default database user, run the following:
sudo -u postgres psql #this opens the postgresql shell. Run the following
ALTER USER postgres WITH PASSWORD 'set-password'; #set-password will be the default user's password.
\q #exits the database shell to ssh shell- Configure the database access Control
By default, PostgreSQL is set to allow connections from localhost only. This default setting can be modified to allow remote connection. Open the pg_hba.conf file as below:
sudo vi /var/lib/pgsql/data/pg_hba.confAdd the following line of code or modify the existing one.
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 md5Besides the above, also confirm and update the settings on postgresql.conf. The file has some settings to block remote connection. Run the following to open the file.
sudo vi /var/lib/pgsql/data/postgresql.conf
#locate the line with listen_addresses. By defult this line is set to the following
listen_addresses = 'localhost'
#To enable remote connection, udpate the line to
listen_addresses = '*'
#Note for local connections only; and you encounter some about being unable to connect to server; update localhost to the localhost IP. see below
listen_addresses = '127.0.0.1' How to create a database user on Postgres
Once the database engine is installed and started, we can now create a new database user. On SSH shell, run the following
sudo -u postgres psql -c "CREATE USER user-name WITH PASSWORD 'user-password';"
#use your prefered values for user-name and user-passwordHow to create a database on Postgres
To create a database, run the following command on SSH shell. Here, we create a database by the name database-name and grant the user created in the steps above all the privileges on this database.
sudo -u postgres psql -c "CREATE DATABASE database-name;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE database-name TO user-name;"
How to connect to Database
After creating the database and the database user, it’s now time to access the database. To connect to the database locally from the server; run
psql -h 127.0.0.1 -U user-name -d database-name #run this from the ssh commandlineTo connect to the database remotely, run the following. Ensure that you have installed Postgres engine on your local machine.
psql -h server-IP -U user-name -d database-namePostgreSQL commands you must know
psql -h <hostname> -U <username> -d <database_name> #to conect to PostgreSQL server
\l #Lists all available database
\c <database_name> #connects to the specified database
\dt #List the current database tables
\d <table_name> #show the tables structure
SELECT * FROM table_name; #select data from table
\q #To exit from postgresql shell
CREATE TABLE table_name (column1 datatype1 constraints,column2 datatype2 constraints); #creates a table
INSERT INTO table_name (column1, column2) VALUES (value1, value2); #insert data into the database
Make a donation to support us
Web hosting and email hosting packages
Related articles:
