PostgreSQL 9.5: A quick start on Fedora 24

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

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 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

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

mydb

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

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.


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

For Developers New in Fedora Using Software

13 Comments

  1. link wiki (/https//fedoraproject.org/wiki/PostgreSQL) is incorrect

  2. You could also do:

    rolectl deploy databaseserver/

    you can also create an initial database and owner using rolectl, if you like:

    echo '{"database":"somedb","owner":"someone","password":"correcthorse"}' | rolectl deploy databaseserver --settings-stdin

    and this is all automatically tested daily as of last week:

    https://openqa.fedoraproject.org/tests/34105

    yay Fedora Server! yay rolekit! 🙂

  3. Thomas Lee

    I installed Postgres . I started the database. I created a database and loaded data.
    I used perl to access the database — in SQLite. How do I get it to work with
    perl and Postgres? I get the following error:

    Can't locate DBI.pm in @INC (you may need to install the DBI module) (@INC contains: /usr/local/lib/perl5 /usr/local/share/perl5 /usr/lib/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib/perl5 /usr/share/perl5 .) at ./LOG/month.pl line 6.
    BEGIN failed--compilation aborted at ./LOG/month.pl line 6.
  4. I use postgresql but tested the docker image and it worked without issue. I haven’t experience any issue using them so far. Thanks for the post.

  5. Night Romantic

    Thank you for the article.

    For a production deployment, it is recommended to use a different set-up
    and harden the service.

    It would be interesting to read about hardening the service in production environment.

  6. I had PostgreSQL 9.4 installed on fedora 23. When I upgraded to fedora 24 , it wasn’t starting with the below error message,

    The data directory was initialized by PostgreSQL version 9.4, which is not compatible with this version 9.5.4.

    I had to delete the existing data directory and create a new one using PostgreSQL 9.5.4 using below command.

    initdb /var/lib/pgsql -E utf8

    Also, mongodb wasn’t starting because default storage engine was changed to WiredTIger and it wasn’t compatible with earlier data. I changed the storage engine to mmapv1 and it worked perfectly.

    Let me know if you have some other solution to these problems.

  7. I had PostgreSQL 9.4 installed on fedora 23. When I upgraded to fedora 24 , it wasn’t starting with the below error message,

    The data directory was initialized by PostgreSQL version 9.4, which is not compatible with this version 9.5.4.

    I had to delete the existing data directory and create a new one using PostgreSQL 9.5.4 using below command.

    initdb /var/lib/pgsql -E utf8

    Also, mongodb wasn’t starting because default storage engine was changed to WiredTIger and it wasn’t compatible with earlier data. I changed the storage engine to mmapv1 and it worked perfectly.

    Let me know if you have some other solution to these problems.

  8. Pavel Raiskup

    If you managed to configure your docker client to be run under non-root user, it is better to just do:

    $ setfacl -m u:26:-wx data
    $ docker run [options] -v

    pwd

    /data:/var/lib/pgsql/data:Z fedora/postgresql

    That allows you to prepare volume directory without being root (or postgres) too.
    The

    chcon

    is not needed with

    :Z

    “mount” suffix.

  9. Thom Lee

    How do you use Postgres with perl?
    I`m using Fedora in 32 bit mode.

  10. Cornel Panceac

    “Now, connect using the superuser by switching to postgres user via su. Set a password for this superuser.”

    At least in F25, you have to first set a password to user ‘postgres’ BEFORE switching to postgres:

    passwd postgres

  11. Matheus Schaefer

    Sorry, but when I do this: sudo postgresql-setup –initdb
    I receive a “command not found” message here… 🙁

Comments are Closed

The opinions expressed on this website are those of each author, not of the author's employer or of Red Hat. Fedora Magazine aspires to publish all content under a Creative Commons license but may not be able to do so in all cases. You are responsible for ensuring that you have the necessary permission to reuse any work on this site. The Fedora logo is a trademark of Red Hat, Inc. Terms and Conditions