PostgreSQL 9.5: A quick start on Fedora 24

PostgreSQL is one of the most popular object-relational database management system (shortened to ORDBMS) and is 100% open-source. It is not purely about relations anymore: PostgreSQL is more and more about NoSQL as well. The following article is a short tutorial to set up PostgreSQL 9.5 on Fedora 24, so it can be used for a development environment. For a production deployment, it is recommended to use a different set-up and harden the service.

The set of PostgreSQL database packages in Fedora’s stable repositories are almost identical to the upstream set of RPMs. There are client tools in the

package. The client library in the

 package is often required by various connectors. The most important part of the database, the daemon, is available in the

package. Some more server-side extensions, tools, or supporting packages may be listed by running the following command in a terminal.

$ dnf list postgresql\*

Basic deployment examples may be found in the article on the Fedora wiki as well. Some first steps are also described in the Fedora Developer Portal.

Basic PostgreSQL setup

Start by installing the packages, initializing the data directory, and starting the daemon.

$ sudo dnf install postgresql-server
$ sudo postgresql-setup --initdb
$ sudo systemctl start postgresql

Now, connect using the superuser by switching to

user via

. Set a password for this superuser.

$ su - postgres
$ psql
 psql (9.5.3)
 Type "help" for help.

 postgres=# \password postgres

Creating a user and a database

It’s not a good idea to connect to the database as

superuser from applications (like you don’t work as the root user in Linux all the time). For that, we’ll need a database and a separate user to access the database. Create them with the following commands.

$ createuser john -P
$ createdb --owner=john mydb

We also want to limit the connections to the server from localhost only. Edit

to look like the following example below to do this.

# TYPE DATABASE USER     ADDRESS      METHOD
 host  all      all      127.0.0.1/32 md5
 host  all      all      ::1/128      md5
 local all      postgres              peer

This configuration allows all users that provide the password (specific to PostgreSQL) to connect from the localhost. It allows the

user (a.k.a. the superuser) to connect in case the same user is authenticated in the operating system (

). More about this is detailed in the upstream documentation.

Now we can restart the PostgreSQL server so the changed configuration applies.

$ sudo systemctl restart postgresql

We’re all set to use the

user to access the database

now.

$ psql -h localhost -U john mydb
Password for user john: 
psql (9.5.3)
Type "help" for help.

mydb=> _

At this point, you can work with the database as you need: create tables, fill then with data, and so on.

PostgreSQL in the container

Linux containers (especially Docker) are slowly approaching production systems. It is also not surprising there is a PostgreSQL Docker image provided by Fedora. The source is found in the Fedora-dockerfiles repository. The image is found in

on Docker Hub. Starting a container for serving PostgreSQL without touching the rest of the system is easy.

Install and run the Docker daemon.

$ sudo dnf install docker
$ sudo systemctl start docker

Pull the image.

$ sudo docker pull fedora/postgresql

Prepare directory for data.

$ sudo mkdir data
$ sudo chown 26:26 data
$ sudo chcon -t svirt_sandbox_file_t data

Start the container with a few arguments. The container uses the prepared directory to store data into and creates a user and database.

$ sudo docker run -v "`pwd`/data:/var/lib/pgsql/data:Z" -e POSTGRESQL_USER=john -e POSTGRESQL_PASSWORD=secret -e POSTGRESQL_DATABASE=mydb -d -p 5432:5432 fedora/postgresql

Now you have PostgreSQL running as a container while storing data into the

directory in the current working directory.

Have some feedback?

That’s all for now! We are happy to hear your experiences with PostgreSQL on Fedora. Did you experience any difficulties? Would you like more versions (not only the latest one)? Anything else regarding the PostgreSQL or different databases? Feel free to use the comments here or approach me directly.

Icons courtesy of: database by Kevin Woodland from the Noun Project, Rocket by Sandra M from the Noun Project