Infrastructure at your Service

Daniel Westermann

Can I do it with PostgreSQL? – 12 – dbca (database configuration assistant)

Short answer: No. Long answer: No. Every other possible answer: No. There is no such tool in PostgreSQL.
In Oracle you can use the database configuration assistant to create your Oracle database. But this is not a must, you can do the same using scripts. Either you use dbca to generate scripts based on the questions you answered in the several screens or you use your own set of scripts to create an Oracle database (You can use dbca templates in addition or you invoke dbca in silent mode and pass your options on the command line). In PostgreSQL the steps to create a PostgreSQL instance are totally different, no surprise here. It already starts with the wording: In PostgreSQL you do not create a database you create a database cluster. Confused? This has nothing to do with multiple nodes are multiple instances as you know it from Oracle (e.g. Real Application Clusters). What a database cluster means are the files on disk (a collection of databases) that are managed by a set of background processes. Cluster just means: belonging together (there are many of types clusters, actually). I’ll use the term database instance from now on. So how can you create a database instance after you installed the PostgreSQL binaries?

When you use the graphical installer provided to the community by EnterpriseDB a PostgreSQL instance will be created automatically after the binaries have been installed. This will create a service on Windows and startup/shutdown scripts on Linux based operating systems (not sure about Mac OS). On Linux when you install the standard way the installer will require root privileges as it needs to add the scripts to startup and shutdown PostgreSQL automatically when then server starts up or does down to the system configuration. Once you started the installer it is just a matter of clicking through the screens:

pg_install_1
pg_install_2
pg_install_3
pg_install_4
pg_install_5
pg_install_6
pg_install_7
pg_install_8
pg_install_9

What happened in the background is that the PostgreSQL instance was created and started:

[email protected]:/home/postgres/ [pg960final] ps -ef | grep postgres | egrep -v "ssh|bash|ps"
postgres  3412     1  0 07:30 ?        00:00:00 /opt/PostgreSQL/9.6/bin/postgres -D /opt/PostgreSQL/9.6/data
postgres  3413  3412  0 07:30 ?        00:00:00 postgres: logger process   
postgres  3415  3412  0 07:30 ?        00:00:00 postgres: checkpointer process   
postgres  3416  3412  0 07:30 ?        00:00:00 postgres: writer process   
postgres  3417  3412  0 07:30 ?        00:00:00 postgres: wal writer process   
postgres  3418  3412  0 07:30 ?        00:00:00 postgres: autovacuum launcher process   
postgres  3419  3412  0 07:30 ?        00:00:00 postgres: stats collector process   
postgres  3805  3579  0 07:56 pts/0    00:00:00 grep --color=auto postgres

In addition the installer created a systemd service (I am on CentOS) so your instance will start up and shutdown automatically (and a postgres user and group, of course):

[email protected]:/home/postgres/ [pg960final] systemctl status postgresql-9.6.service
● postgresql-9.6.service - PostgreSQL 9.6 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2017-03-31 07:30:01 CEST; 28min ago
  Process: 3410 ExecStart=/opt/PostgreSQL/9.6/bin/pg_ctl start -w -t ${TimeoutSec} -D /opt/PostgreSQL/9.6/data -l /opt/PostgreSQL/9.6/data/pg_log/startup.log (code=exited, status=0/SUCCESS)
 Main PID: 3412 (postgres)
   CGroup: /system.slice/postgresql-9.6.service
           ├─3412 /opt/PostgreSQL/9.6/bin/postgres -D /opt/PostgreSQL/9.6/data
           ├─3413 postgres: logger process   
           ├─3415 postgres: checkpointer process   
           ├─3416 postgres: writer process   
           ├─3417 postgres: wal writer process   
           ├─3418 postgres: autovacuum launcher process   
           └─3419 postgres: stats collector process   

From now on you can connect to the instance and start your journey:

[email protected]:/home/postgres/ [pg960final] /opt/PostgreSQL/9.6/bin/psql postgres
Password: 
psql.bin (9.6.2)
Type "help" for help.

postgres= \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 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
(3 rows)

postgres=

But this is not the only method for creating a PostgreSQL instance. On Linux most of the distributions provide PostgreSQL packages in their repositories which you then can install using yum (when you are on RedHat based systems) or apt (when you are on Debian based systems (There are others as well, such as YaST on SuSE but yum and apt are the most popular, I believe). In my case, using the official CentOS repositories, I can use yum and check what CentOS provides:

[email protected]:/home/postgres/ [pg960final] yum search postgresql
...
postgresql.i686 : PostgreSQL client programs
postgresql.x86_64 : PostgreSQL client programs
postgresql-contrib.x86_64 : Extension modules distributed with PostgreSQL
postgresql-devel.i686 : PostgreSQL development header files and libraries
postgresql-devel.x86_64 : PostgreSQL development header files and libraries
postgresql-docs.x86_64 : Extra documentation for PostgreSQL
postgresql-jdbc.noarch : JDBC driver for PostgreSQL
postgresql-jdbc-javadoc.noarch : API docs for postgresql-jdbc
postgresql-libs.i686 : The shared libraries required for any PostgreSQL clients
postgresql-libs.x86_64 : The shared libraries required for any PostgreSQL clients
postgresql-odbc.x86_64 : PostgreSQL ODBC driver
postgresql-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql-plpython.x86_64 : The Python2 procedural language for PostgreSQL
postgresql-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql-test.x86_64 : The test suite distributed with PostgreSQL
postgresql-upgrade.x86_64 : Support for upgrading from the previous major release of PostgreSQL
...

The packagers decided to split PostgreSQL into several packages as you can see above. If you only want to install the core server you would:

[[email protected] ~] yum install postgresql-server.x86_64

In this case the instance will not be created automatically:

[[email protected] ~] ps -ef | grep postgres
root      9981  4558  0 08:18 pts/0    00:00:00 grep --color=auto postgres

But, as with the graphical installer above, a systemd service was created for you:

[[email protected] ~] systemctl list-unit-files | grep postgres
postgresql.service                            disabled

Then we can just enable and start the service?

[[email protected] ~] systemctl enable postgresql.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.
[[email protected] ~] systemctl start postgresql.service
Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.

Not really. What the installation with yum did not do for you is to create the instance. The good thing with the CentOS provided packages is that you get helpers to create the instance which are not there by default. For creating the instance you would:

-bash-4.2$ sudo postgresql-setup initdb
Initializing database ... OK

… and your instance got created but not yet started. To start it:

-bash-4.2$ sudo systemctl start postgresql.service
-bash-4.2$ psql postgres
psql (9.2.18)
Type "help" for help.

Quite easy. But here you can see one issue with the packages provided by the Linux distributions. What was installed in my case is PostgreSQL 9.2.18 and there are some points to consider with that: The latest PostgreSQL 9.2 release is 9.2.20 (as of today). So you miss 2 minor versions containing bug fixes. Even more important PostgreSQL 9.2 will go out of support this September because it was released almost 5 years ago, the current release is 9.6.2 (supported until September 2021). Not a good choice for running a production server.

Luckily there is another possibility: The PostgreSQL community provides repositories as well. There is one for yum based systems and one for apt based systems. For getting the yum based repositories into CentOS you would (for PostgreSQL 9.6):

[[email protected] ~] wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
[[email protected] ~] yum localinstall pgdg-centos96-9.6-3.noarch.rpm 

When you check what is available now you’ll find this:

postgresql96.x86_64 : PostgreSQL client programs and libraries
postgresql96-contrib.x86_64 : Contributed source and binaries distributed with PostgreSQL
postgresql96-debuginfo.x86_64 : Debug information for package postgresql96
postgresql96-devel.x86_64 : PostgreSQL development header files and libraries
postgresql96-docs.x86_64 : Extra documentation for PostgreSQL
postgresql96-libs.x86_64 : The shared libraries required for any PostgreSQL clients
postgresql96-odbc.x86_64 : PostgreSQL ODBC driver
postgresql96-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql96-plpython.x86_64 : The Python procedural language for PostgreSQL
postgresql96-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql96-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql96-tcl.x86_64 : A Tcl client library for PostgreSQL
postgresql96-tcl-debuginfo.x86_64 : Debug information for package postgresql96-tcl
postgresql96-test.x86_64 : The test suite distributed with PostgreSQL

Installation is the same as with the CentOS repositories above:

[[email protected] ~] yum install postgresql96-server.x86_64
[[email protected] ~] systemctl list-unit-files | grep postgres
postgresql-9.6.service    
[[email protected] ~] systemctl enable postgresql-9.6.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.6.service to /usr/lib/systemd/system/postgresql-9.6.service.
[[email protected] ~] ls /usr/pgsql-9.6/bin/initdb ^C
[[email protected] ~] su - postgres
Last login: Fri Mar 31 08:29:10 CEST 2017 on pts/1
-bash-4.2$ /usr/pgsql-9.6/bin/initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/9.6/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data -l logfile start

-bash-4.2$ sudo systemctl start postgresql-9.6.service
-bash-4.2$ /usr/pgsql-9.6/bin/psql postgres
psql (9.6.2)
Type "help" for help.

postgres= \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 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
(3 rows)

And ready. The packages provided by the PostgreSQL community are a much better choice than the ones provided by your distributions because here you get the latest ones.

What we did above as an extra step was calling a utility which is named “initdb”. initdb is the tool you use to create the PostgreSQL instance after you installed the binaries. The graphical installer uses initdb in the background as well as there is no other way to do it. When you compile and install PostgreSQL from source you would need to use initdb to create your PostgreSQL instance as well.

Conclusion: Depending on how you install PostgreSQL onto your system the PostgreSQL instance is created automatically (when you use the installer provided by EnterpriseDB), almost automatically when you use the packages provided by your distribution and almost automatically when you use the packages provided directly by the PostgreSQL community. In the next post we’ll look at initdb in more detail.

2 Comments

  • What a pity you don’t seem to understand what a database cluster is on PostgreSQL. It does NOT mean a bunch of files, but – I cite the documentation here:

    A database cluster is a collection of databases that is managed by a single instance of a running database server.

    One of the big differences between Oracle and PostgreSQL is that you can create a new database (within a cluster) easily with the command “CREATE DATABASE newdb;”. And of course you can have several clusters on one machine, each listening to a different port.

    • Daniel Westermann says:

      Hi Holger,

      I do not really get what is the difference in what you are saying compared to what wrote here: “What a database cluster means are the files on disk (a collection of databases) that are managed by a set of background processes. Cluster just means: belonging together (there are many of types clusters, actually). I’ll use the term database instance from now on. ”

      From the official documentation (https://www.postgresql.org/docs/9.6/static/creating-cluster.html): “Before you can do anything, you must initialize a database storage area on disk. We call this a database cluster.”

      But anyway, thanks for you comment, I am always open to correct something in case it is not clear.

      Cheers,
      Daniel
      I am open for discussions …

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure