The initdb Command in PostgreSQL

Bilal Shahid Sep 07, 2022
  1. the initdb Command in PostgreSQL
  2. Use of the initdb Command in PostgreSQL
The initdb Command in PostgreSQL

The user can manually set up the PostgreSQL server using different commands. First, you would require to set up a database cluster, which has been explained throughout the article.

One of the most crucial BASH commands in the initialization process is the initdb command. Therefore, this article puts particular focus on the understanding of the initdb command.

It is crucial to understand the initdb command before setting up PostgreSQL manually. If PostgreSQL is not set up correctly in the initial stage, it will not execute queries in the later stage.

In the worst case, the PostgreSQL server may not be able to detect the data directory path and fail to start up. Therefore, this article explains the initdb command and a little more detail about the database cluster.

the initdb Command in PostgreSQL

The initdb command is used in the initialization process of a database cluster. After the initialization process using the initdb command, a database named postgres will be created inside the database cluster.

The postgres works as a default database used by the users, utilities, and third-party applications. In addition to creating the postgres database inside the cluster, another database named template1 is also created inside each cluster.

The template1 database works as a template for databases created later inside the cluster. Remember, a database cluster is the data directory under file systems.

The database cluster is a single directory where all the data is stored. Therefore, the initdb command takes a location as an argument where it initializes the database cluster.

Any location can be provided as an argument for the database cluster. Data is stored at that desired location provided with the initdb command.

A -D option is used with the initdb command to designate the desired location for the database cluster. Hence, one must select the location of the database cluster carefully.

Use of the initdb Command in PostgreSQL

The definition of the initdb command has been explained in the section above. In addition to the description, the work behind the command has been described in detail in the previous section.

This section covers the execution of the initdb command thoroughly. First, ensure you are logged into the PostgreSQL user account before executing the initdb command.

The initdb command works as follows:

> initdb -D /usr/local/pgsql/data

The pg_ctl is used to start or stop the server. We can also use it to run the initdb command as shown below:

> pg_ctl -D /usr/local/pgsql/data initdb

The initdb command initializes the default locale for the database cluster. In addition, the initdb command also specifies the locale environment settings and the default character set encoding.

Although, the user can specify a different locale as well. The initdb command creates a directory at the specified location but fails if:

  • It does not have permission to write in the parent directory.
  • The data directory already exists in that location.

It is preferred that the PostgreSQL user should own the parent directory in addition to the data directory. However, if the parent directory doesn’t exist, it should be created by the user.

If the grandparent directory is not writable, we can use root privilege to create the parent and child directory inside the grandparent directory. We can use the following commands:

> root# mkdir /usr/local/pgsql
> root# chown postgres /usr/local/pgsql
> root# su postgres
> postgres$ initdb -D /usr/local/pgsql/data

Ensure Authorized Access to the Database Cluster

The initdb command uses the default client authentication setup while initializing and creating the database cluster. Then, it allows all the local users to connect to the database.

The local users can also become the database’s superusers. It is ideal to set a password to the database superuser to avoid unauthorized access to a database.

It can be done using any of the following commands:

  • The initdb command with the -W extension
  • The --pwprompt command
  • The pwfile command

We can use the above commands to set a password to the database. It allows only authorized users to access the database.

Initializing Dependencies Error

If you face any initializing dependency error, such as the Bus Error or the Exit Code 135 Error, initialize the database cluster using the procedure mentioned below.

Once the PostgreSQL server and client tools have been installed, the user needs to run the following commands as ROOT(su). Start with the service postgresql initdb command.

It initializes the PostgreSQL database. The following commands ensure that the PostgreSQL server is running correctly:

> service postgresql initdb
> systemctl enable postgresql
> systemctl start postgresql

The commands initialize the PostgreSQL database. But, first, we must verify that the PostgreSQL server lies in the directory /var/lib/pgsql.

In addition, ensure that the PostgreSQL server is running. For example, the command ps -ef | grep postgres can be used to check for the running state of the PostgreSQL server.

If the errors do not resolve, try the following methods:

  • We should remove the empty install data directories.
  • All log files should be precisely read.
  • We can modify the PostgreSQL server’s user or password.
  • We can clean out the data directory of the PostgreSQL server.

Remember that the initdb command in the PostgreSQL server initializes the database cluster and sets it in the user’s desired location. The database is initialized with the locale environment settings and can be changed upon user request.

Author: Bilal Shahid
Bilal Shahid avatar Bilal Shahid avatar

Hello, I am Bilal, a research enthusiast who tends to break and make code from scratch. I dwell deep into the latest issues faced by the developer community and provide answers and different solutions. Apart from that, I am just another normal developer with a laptop, a mug of coffee, some biscuits and a thick spectacle!

GitHub