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