Installing PostgreSQL and Configuration

TYPES OF COMPUTER-BASED ON CONFIGURATION AND SIZE
June 22, 2021
Show all

Installing PostgreSQL and Configuration

Installing PostgreSQL is easy but user creation and setting for user password can create issue. You can install the PostgreSQL on Linux server by using some command that I have mention here. I have used Centos Server so I have shared centos server commands.
I have shared step by step installation and configuration.

Step 1 — Installing PostgreSQL

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

for checking

yum list postgresql*

Step 2 — Creating a New PostgreSQL Database Cluster

sudo yum install postgresql11-server

I have used PostgreSQL-11 version or you can use here PostgreSQL  version that you want to install .

sudo /usr/pgsql-11/bin/postgresql-11-setup initdb

sudo systemctl start postgresql-11

sudo systemctl enable postgresql-11

Step 3 — Using PostgreSQL Roles and Databases

Switch over to the postgres account on your server by typing:

sudo -i -u postgres

psql

sudo -u postgres psql

Step 4 — Creating a New Role

If you are logged in as the postgres account, you can create a new user by typing:

createuser –interactive

sudo -u postgres createuser –interactive

The script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user to your specifications. For this tutorial, create a sammy user and give it superuser privileges:

Output

Enter name of role to add: sammy

Shall the new role be a superuser? (y/n) y

Step 5 — Creating a New Database

createdb sammy

sudo -u postgres createdb sammy

Step 6 — Opening a Postgres Prompt with the New Role

If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the postgres user):

sudo adduser sammy

Once this new account is available, you can either switch over and connect to the database by typing:

sudo -i -u sammy

psql

Or, you can do this inline:

sudo -u sammy psql

This command will log you in automatically.

If you want your user to connect to a different database, you can do so by specifying the database like this:

psql -d postgres

Once logged in, you can check your current connection information by typing:

\conninfo

Configure PostgreSQL to allow remote connection

Configuring postgresql.conf

In order to fix this issue we need to find postgresql.conf. In different systems it is located at different place. I usually search for it.

$ find / -name “postgresql.conf”

/var/lib/pgsql/9.4/data/postgresql.conf location may be change in you server

Open postgresql.conf file and replace line

listen_addresses = ‘localhost’

with

listen_addresses = ‘*’

Now restart postgresql server.

Configuring pg_hba.conf

Let’s try to connect to a remote PostgreSQL server using “psql”.

In order to fix it, open pg_hba.conf and add the following entry at the very end.

host    all             all              0.0.0.0/0                       md5

host    all             all              ::/0                            md5

To change the Postgres user’s password follow these steps

Login into the psql:

$ sudo -u postgres psql

Then in the psql console change the password and quit:

postgres=# \password postgres

Enter new password: <new-password>

postgres=# \q

or using a query

ALTER USER postgres PASSWORD ‘<new-password>’;

or in one line

sudo -u postgres psql -c “ALTER USER postgres PASSWORD ‘<new-password>’;”

sudo service postgresql restart

For more details and version of PostgreSQL you can click here for knowledge more about Linux servers click here

Leave a Reply

Your email address will not be published. Required fields are marked *