Original Post // http://www.lindoe.com

Install PostgreSQL (Install From the CentOS Repositories)

As of this writing, the CentOS 7 Repositories ship with PostgreSQL version 9.2.15. To Install from the CentOS Repositories, simply run :

$ sudo yum install postgresql-server postgresql-contrib

Dependencies Resolved

====================================================================================================
 Package                      Arch              Version                    Repository          Size
====================================================================================================
Installing:
 postgresql-server            x86_64            9.2.23-3.el7_4             updates            3.8 M
Installing for dependencies:
 postgresql                   x86_64            9.2.23-3.el7_4             updates            3.0 M
 postgresql-libs              x86_64            9.2.23-3.el7_4             updates            234 k

Transaction Summary
====================================================================================================
Install  1 Package (+2 Dependent packages)

Initialize your Postgres database and start PostgreSQL:

$ sudo postgresql-setup initdb

Initializing database ... OK

$ sudo systemctl start postgresql

Optional: Configure PostgreSQL to start on boot:

$ sudo systemctl enable postgresql

Remove PostgreSQL

$ sudo yum remove postgresql\*
$ sudo mv /var/lib/pgsql /var/lib/old.pgsql

Configure PostgreSQL

Secure the Postgres User

By default, PostgreSQL will create a Linux user named postgres to access the database software.

Caution

The postgres user should not be used for other purposes (e.g., connecting to other networks). Doing so presents a serious risk to the security of your databases.

Change the postgres user's Linux password:

sudo passwd postgres

Issue the following commands to set a password for the postgres database user. Be sure to replace newpassword with a strong password and keep it in a secure place.

su - postgres
psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword'; "

Access the PostgreSQL Shell

The PostgreSQL client shell allows you to issue SQL commands to administer your databases. As the postgres Linux user, log in by running:

psql postgres

This will log you in as the postgres database user. You'll see a prompt similar to this:

-bash-4.2$ psql postgres
psql (9.2.23)
Type "help" for help.

postgres=#

In the last line, postgres=# indicates the name of the current database. To see a list of available commands, type \h . You may find more information on a specific command by adding it after \h .

Once you've finished using the shell, you can exit with \q .

Managing Database

List Databases

From the Postgres shell, you can list all of your database with the \l or \list command. You will receive output similar to this:

psql (9.2.23)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 dspace    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=#

You may also show the current database and user by entering \c from the Postgres shell. Additional info, like socket and port, will be included if you use \conninfo :

psql (9.2.23)
Type "help" for help.

postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=#

Connect to a Database

You can use the psql command to connect to a specific database.

1. Connect to the test database:

psql dspace

2. You will see the following output:

psql (9.2.23)
Type "help" for help.

dspace=#

By default, you will connect to a database as your peer-authenticated user. Hower, if you've enabled local password access, It's also possible to specify which user you wish to connect as:
psql dspace -U dspacerole
You'll be prompted to enter the password for the dspacerole database user before you access the shell.

CREATE ROLE username WITH LOGIN PASSWORD 'quoted password'

Create a Database

You can create databases with the createdb command. Create a sample database called dspace by running this command as the postgres Linux user :

create database [database_name]

createdb dspace

It's also possible to assign ownership of the database to specific Postgres user/role. For example, you could assign ownership to the dspacerole role by running:

createdb dpsace -O dspacerole

The createdb command has several additional options. which can be found in the PostgreSQL Documentation.

Delete a Database

You can delete, or drop, database with the dropdb command. For example, to delete the dspace database created previously, issue this command as the postgres Linux user:

dropdb dspace

Caution

Delete databases cannot be recovered.

CentOS 7 and PostgreSQL