The PostgreSQL relational database system is a powerful, scalable, and standards-compliant open-source database platform. This guide will help you install and configure PostgreSQL on your CentOS 7 Linode.
Before You Begin
- Familiarize yourself with our Getting Started guide and complete the steps for setting your Linode’s hostname and timezone.
- Complete the sections of our Securing Your Server guide to create a standard user account, harden SSH access and remove unnecessary network services.
- Update your system:
sudo yum update
NoteThis guide is written for a non-root user. Commands that require elevated privileges are prefixed with sudo. If you’re not familiar with the sudo command, visit the Users and Groups guide for more information.
Install PostgreSQL
In this section, we’ll cover two different methods for installing PostgreSQL. If you do not need the latest version, we recommend using the first method to install PostgreSQL from the CentOS repositories.
Unless otherwise noted, the instructions in subsequent sections of this guide will be compatible with versions installed by either method.
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 - Initialize your Postgres database and start PostgreSQL:
sudo postgresql-setup initdb sudo systemctl start postgresql - Optional: Configure PostgreSQL to start on boot:
sudo systemctl enable postgresql
Install From the Postgres Repositories
Alternatively, you can install the latest version from the Postgres repositories. As of this publication, PostgreSQL 9.6.3 is the most recent version available for CentOS 7, but these steps can be applied to any RPM-based installation.
NoteWhen Postgres is installed using this method, the version number is included in its configuration directories. For example, /var/lib/pgsql becomes /var/lib/pgsql/9.6. This is also the case with systemd units; systemctl status postgresql becomes systemctl status postgresql-9.6.
- Select the version you wish to install from the Postgres Yum repositories. Locate the CentOS 7 link for your chosen version and download it to your Linode:
wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm - Install the RPM, as well as the EPEL repositories, which will be used to satisfy dependencies:
sudo yum install pgdg-centos96-9.6-3.noarch.rpm epel-release - Update Yum to apply your changes and install PostgreSQL. When installing Postgres manually, you will have to specify the version:
sudo yum update sudo yum install postgresql96-server postgresql96-contrib - Initialize your database and start PostgreSQL:
sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb sudo systemctl start postgresql-9.6 - Optional: Configure PostgreSQL to start on boot:
sudo systemctl enable postgresql-9.6
Configure PostgreSQL
Secure the Postgres Users
By default, PostgreSQL will create a Linux user named postgres to access the database software.
CautionThe 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
postgresuser’s Linux password:sudo passwd postgres - Issue the following commands to set a password for the
postgresdatabase user. Be sure to replacenewpasswordwith a strong password and keep it in a secure place.su - postgres psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword';"Note that this user is distinct from thepostgresLinux user. The Linux user is used to access the database, and the PostgreSQL user is used to perform administrative tasks on the databases.The password set in this step will be used to connect to the database via the network. Peer authentication will be used by default for local connections. See the Secure Local PostgreSQL Access section for information about changing this setting.
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:
psql (9.2.15)
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.
Work with Databases
This section will cover how to create, delete and access databases.
Create a Database
You can create databases with the createdb command. Create a sample database called mytestdb by running this command as the postgres Linux user:
createdb mytestdb
It’s also possible to assign ownership of the database to a specific Postgres user/role. For example, you could assign ownership to the examplerole role by running:
createdb mytestdb -O examplerole
The createdb command has several additional options, which can be found in the PostgreSQL documentation.
Connect to a Database
You can use the psql command to connect to a specific database.
- Connect to the test database:
psql mytestdb - You will see the following output:
psql (9.2.15) Type "help" for help. mytestdb=#By default, you will connect to a database as your peer-authenticated user. However, if you’ve enabled local password access, it’s also possible to specify which user you wish to connect as:psql mytestdb -U exampleroleYou’ll be prompted to enter the password for theexampleroledatabase user before you access the shell.
List Databases
From the Postgres shell, you can list all of your databases with the \l or \list command. You will receive output similar to this:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mytestdb | 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)
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:
You are connected to database "mytestdb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
Delete a Database
You can delete, or drop, databases with the dropdb command. For example, to delete the mytestdb database created previously, issue this command as the postgres Linux user:
dropdb mytestdb
CautionDeleted databases cannot be recovered.
Work With Tables
PostgreSQL databases use tables to store and organize information within a database. In this section, you’ll find practical examples for adding, removing and manipulating tables. Unless otherwise noted, the commands in this section should be issued from the Postgres shell once you’ve connected to your database.
Create Tables
This section contains examples that create a test database with an employee’s first and last name, assigning each a unique key. When creating your own tables, you may specify as many parameters (columns) as you need and name them appropriately.
- Create a table called “employees” in your test database:
CREATE TABLE employees (employee_id int, first_name varchar, last_name varchar); - Insert a record into the table:
INSERT INTO employees VALUES (1, 'John', 'Doe');
View the Content of a Table
To view the contents of the “employees” table:
SELECT * FROM employees;
This produces the following output:
employee_id | first_name | last_name
-------------+------------+-----------
1 | John | Doe
(1 row)
List Tables in a Database
You can list all tables in the current database with the \dt command:
mytestdb-# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | employees | table | postgres
Delete Tables
Delete tables with DROP TABLE. To delete the employees table:
DROP TABLE employees;
DROP TABLE accepts multiple comma-separated table names as arguments. For example, if you had two separate tables called employees1 and employees2, you could delete them both by running:
DROP TABLE employees1, employees2;
Add Columns
Tables can be altered to add definitions, data types and columns. In this example you’ll add a new start_date column that uses the date data type.
- Add the
start_datecolumn to theemployeestable:ALTER TABLE employees ADD start_date date; - Verify your change:
SELECT * FROM employees;You’ll see that the new column has been created, but it does not contain any data:employee_id | first_name | last_name | start_date -------------+------------+-----------+------------ 1 | John | Doe | (1 row)In this example you’ve used thedatedata type, but PostgreSQL tables support several different types of data. See the PostgreSQL Documentation for a full explanation of supported data types.
Add and Update Rows
In this section, you’ll use UPDATE to enter a value into the existing row you’ve created. Then, you’ll create an entirely new row with INSERT.
- Update the
start_datefield for the user with the value1in theemployee_idcolumn:UPDATE employees SET start_date = '2016-09-28' WHERE employee_id = '1'; - Create a new row in the
employeestable:INSERT INTO employees VALUES (2, 'Jane', 'Smith', '2015-03-09'); - Verify your changes:
SELECT * FROM employees;You’ll see that the start date of2016-09-28has been added to the first row, and that a new row has been created for “Jane Smith”:employee_id | first_name | last_name | start_date -------------+------------+-----------+------------ 1 | John | Doe | 2016-09-28 2 | Jane | Smith | 2015-03-09 (2 rows)
Remove Columns and Rows
In this section, you’ll remove a column from your table and then remove the second row.
- Use
ALTER TABLEto remove thestart_datecolumn you made previously:ALTER TABLE employees DROP start_date; - Now use
DELETEto remove the second row of youremployeestable. The following command will remove the row with a value of2in theemployee_idcolumn:DELETE FROM employees WHERE employee_id = '2'; - Confirm your changes:
SELECT * FROM employees;Your table now consists of a single row, with thestart_datecolumn removed:employee_id | first_name | last_name -------------+------------+----------- 1 | John | Doe (1 row)
Query a Table
You can use queries to pull specific information from your database. This command will query your employees table to only return values for the employee_id and last_name columns:
SELECT last_name,employee_id FROM employees;
You’ll receive an output similar to this:
last_name | employee_id
-----------+-------------
Doe | 1
(1 row)
PostgreSQL supports many querying options. See the PostgreSQL Documentation for more information.
Work With Roles
PostgreSQL grants database access via roles, which are used to specify privileges. Roles can be understood as having a similar function to Linux “users.” In addition, roles may also be created as a set of other roles, similar to a Linux “group.” PostgreSQL roles apply globally, so you will not need to create the same role twice if you’d like to grant it access to more than one database on the same server.
Create Roles
New user roles are added with the createuser command. To create a new user called examplerole, issue this command as the postgres Linux user:
createuser examplerole --pwprompt
You will be prompted to create a password for the new user.
Give a Role Access to a Database
In this example, you’ll give the newly created examplerole user access to your database.
- Connect to the database:
psql mytestdbYou’ll be connected as thepostgresdatabase user by default. - From the PostgreSQL shell, enter the following to grant all privileges on the table
employeesto the userexamplerole:GRANT ALL ON employees TO examplerole; - Exit the database with
\q.
List All Roles
You can list all roles from the Postgres Shell by running \du. You’ll see an output similar to this:
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------+-----------
examplerole | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
Group Roles
For ease of administration, it’s possible to add multiple user roles to a single group, so that their privileges can be managed as a whole. In this section you’ll create a new group and add the examplerole user to it. These commands should be run as the postgres Linux user.
- Use the
createusercommand to create a new group role. The--no-loginoption is specified because groups do not need login capability.createuser examplegroup --no-login - Log into the Postgres shell and add
exampleroleto the new group:psql postgres GRANT examplegroup TO examplerole; - From the Postgres shell, verify your changes with
\du. You’ll see that theexampleroleuser is now listed as a member of theexamplegroupgroup:postgres=# \du List of roles Role name | Attributes | Member of --------------+------------------------------------------------+---------------- examplegroup | Cannot login | {} examplerole | | {examplegroup} group | | {} postgres | Superuser, Create role, Create DB, Replication | {}Thecreateusercommand has several other options. See the PostgreSQL documentation for more details. - When you’ve finished applying your changes, exit the Postgres shell with
\q.
Alter Roles
While specific settings and privileges can be applied to a role when it’s created, you can also modify a role’s properties later on. In this example, we’ll modify the examplerole user so that it can create new databases. The commands in this section should be run as the postgres Linux user.
- Log in as the
postgresdatabase user:psql postgres - From the Postgres shell, add the
CREATEDBparameter to theexampleroleuser:ALTER ROLE examplerole CREATEDB;A number of permissions can be applied when creating or altering a role. See the PostgreSQL Documentation for more details. - Use
\duto confirm your changes. You’ll see that the “Create DB” attribute is listed next to theexampleroleuser:postgres=# \du List of roles Role name | Attributes | Member of -------------+------------------------------------------------+----------- examplerole | Create DB | {} group | | {} postgres | Superuser, Create role, Create DB, Replication | {} - Once you’ve finished, exit the Postgres shell with
\q.
Delete Roles
The dropuser command is used to delete PostgreSQL roles. To delete the examplerole user, issue this command as the postgres Linux user:
dropuser examplerole
Peer Authentication
PostgreSQL uses peer authentication by default. This means that database connections will be granted to local system users if their Linux username matches the name of their PostgreSQL role. To make use of peer authentication effectively, you would need to create both a Linux user and a corresponding PostgreSQL role. For the examplerole role you just created, you can use peer authentication by creating an examplerole local system user. This command must be run as a user with sudo access:
sudo adduser examplerole && passwd examplerole
Note that you will be prompted to create a password for the new examplerole Linux user. Alternatively, you can follow our steps to secure local access.
Secure PostgreSQL
Secure Local Access
While PostgreSQL’s default peer authentication is useful in cases where a particular system user will be running a local program (e.g., scripts, CGI/FastCGI processes owned by separate users, etc.), you may wish to require passwords for greater security.
Commands in this section should be run as the postgres Linux user unless otherwise specified.
- Edit the
/var/lib/pgsql/data/pg_hba.conffile, under the# "local" is for Unix domain socket connections onlyheader:File: /var/lib/pgsql/data/pg_hba.conf1 2# "local" is for Unix domain socket connections only local all all peerReplacepeerwithmd5on this line to activate password authentication using an MD5 hash.NoteIf you installed PostgreSQL from the Postgres repositories, you will need to specify your version number in this file path, for example:/var/lib/pgsql/9.6/data/pg_hba.conf. - To enable these changes, you need to restart PostgreSQL. However, you did not grant the
postgresuser sudo privileges for security reasons. Return to the normal user shell:exit - Restart PostgreSQL and switch back to the
postgresuser:sudo systemctl restart postgresql su - postgres - As
postgres, connect to the test database as theexamplerolePostgreSQL user:psql mytestdb -U exampleroleYou will be prompted to enter the password for theexampleroleuser and then givenpsqlshell access to the database. When using a database, you may check access privileges for each of its tables with the\zcommand.
Secure Remote Access
PostgreSQL listens for connections on localhost by default, and it is not advised to reconfigure it to listen on public IP addresses. If you wish to make PostgreSQL externally accessible, it’s recommended that you follow the Postgres documentation for using SSL to secure your remote connections. Alternatively, you could connect to PostgreSQL over an SSH tunnel. To access your databases remotely using a graphical tool, please follow one of these guides:
