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:
What happened in the background is that the PostgreSQL instance was created and started:
postgres@pgbox:/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):
postgres@pgbox:/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:
postgres@pgbox:/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:
postgres@pgbox:/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:
[root@centos7 ~] yum install postgresql-server.x86_64
In this case the instance will not be created automatically:
[root@centos7 ~] 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:
[root@centos7 ~] systemctl list-unit-files | grep postgres postgresql.service disabled
Then we can just enable and start the service?
[root@centos7 ~] systemctl enable postgresql.service Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service. [root@centos7 ~] 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):
[root@centos7 ~] wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm [root@centos7 ~] 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:
[root@centos7 ~] yum install postgresql96-server.x86_64
[root@centos7 ~] systemctl list-unit-files | grep postgres
postgresql-9.6.service
[root@centos7 ~] 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.
[root@centos7 ~] ls /usr/pgsql-9.6/bin/initdb ^C
[root@centos7 ~] 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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)