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
postgresql package. The client library in the
postgresql-libs package is often required by various connectors. The most important part of the database, the daemon, is available in the
postgresql-server 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 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
postgres user via
su. 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
postgres 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
/var/lib/pgsql/data/pg_hba.conf 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
postgres user (a.k.a. the superuser) to connect in case the same user is authenticated in the operating system (
su - postgres). 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
john user to access the database
$ 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
fedora/postgresql 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
data 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.