Skip to main content

InitDB uses in Postgresql

 It will create a new PostgreSQL cluster.

  • In Postgres cluster is a collection of databases that are managed by a single cluster.
  • It will create the data directories in which the database data will live, generating the shared catalog tables and creating default databases.

* Postgresql — used for connections

* template 0 — user connection is not allowed and maintain DB consistency

* template 1 — When we creating a database will take an exact copy of template1.

  • In case the user doesn’t have permission on the data directory will create an empty directory.
  • initdb initializes the database cluster with default locale and character set encoding. This character set encoding, collation order (LC_COLLATE) and character set classes (LC_CTYPE, e.g., upper, lower, digit) can be set separately for a database when it is created. initdb determines those settings for the template1 database, which will serve as the default for all other databases.
  • Before the Initdb initialises, the data directory was empty.
[postgres@prime]$cd /u01/pgdatabase/data[postgres@prime data]$ ls -lrthtotal of 0
  • Now we are going to execute the initdb -D “ path ”, here -D specifies data directory location
postgres@prime data]$ /u01/postgresql-10.0/bin/initdb -D /u01/pgdatabase/data
  • Once the initdb initialised, to verify the data directory.
[postgres@prime data]$ ls -lrthtotal 124K-rw------- 1 postgres postgres 88 Oct 30 2019 postgresql.auto.conf-rw------- 1 postgres postgres 3 Oct 30 2019 PG_VERSIONdrwx------ 2 postgres postgres 4.0K Oct 30 2019 pg_twophasedrwx------ 2 postgres postgres 4.0K Oct 30 2019 pg_tblspcdrwx------ 2 postgres postgres 4.0K Oct 30 2019 pg_snapshotsdrwx------ 2 postgres postgres 4.0K Oct 30 2019 pg_serialdrwx------ 2 postgres postgres 4.0K Oct 30 2019 pg_replslotdrwx------ 4 postgres postgres 4.0K Oct 30 2019 pg_multixact-rw------- 1 postgres postgres 1.6K Oct 30 2019 pg_ident.conf-rw------- 1 postgres postgres 4.5K Oct 30 2019 pg_hba.confdrwx------ 2 postgres postgres 4.0K Oct 30 2019 pg_dynshmemdrwx------ 2 postgres postgres 4.0K Oct 30 2019 pg_commit_tsdrwx------ 2 postgres postgres 4.0K Oct 30 2019 pg_xactdrwx------ 2 postgres postgres 4.0K Oct 30 2019 pg_subtrans-rw------- 1 postgres postgres 23K Feb 14 22:17 postgresql.confdrwx------ 11 postgres postgres 4.0K Feb 14 22:43 basedrwx------ 2 postgres postgres 4.0K Feb 14 22:44 pg_stat-rw------- 1 postgres postgres 769 Feb 14 22:46 logfiledrwx------ 3 postgres postgres 4.0K Mar 2 11:40 pg_wal-rw------- 1 postgres postgres 62 Mar 7 13:38 postmaster.optsdrwx------ 2 postgres postgres 4.0K Mar 7 13:38 pg_notify-rw------- 1 postgres postgres 78 Mar 7 13:38 postmaster.piddrwx------ 2 postgres postgres 4.0K Mar 7 13:46 globaldrwx------ 4 postgres postgres 4.0K Mar 7 15:43 pg_logicaldrwx------ 2 postgres postgres 4.0K Mar 8 11:33 pg_stat_tmp

Here the important files is created automatically i.e  postgresql.conf, pg_hba.conf,base,pg_tblspc ..etc

Summary:

The initdb use to create the data directories and default databases as well. By using initdb we can create multiple clusters on the same machine with the different directory path.

Comments

Popular posts from this blog

PostgreSQL Vacuum and Vacuum full are not two different processes

  PostgreSQL’s   VACUUM   and   VACUUM FULL   are not separate processes but rather different operational modes of the same maintenance command. Here’s why: Core Implementation Both commands share the same underlying codebase and are executed through the  vacuum_rel()  function in PostgreSQL’s source code ( src/backend/commands/vacuum.c ). The key distinction lies in the  FULL  option, which triggers additional steps: Standard  VACUUM : Removes dead tuples (obsolete rows) and marks space reusable  within PostgreSQL Updates the visibility map to optimize future queries Runs concurrently with read/write operations VACUUM FULL : Rewrites the entire table into a new disk file, compressing it and reclaiming space for the  operating system Rebuilds all indexes and requires an  ACCESS EXCLUSIVE  lock, blocking other operations Key Differences in Behavior Aspect Standard VACUUM VACUUM FULL Space Reclamation Internal reuse onl...

PostgreSQL Health Check - Performance Audit and Recommendations

   PostgreSQL Server Capacity Planning and Optimization: Conduct a thorough assessment of current and projected workload demands on the database server's critical resources. This evaluation should cover key factors such as CPU utilization, RAM consumption, storage requirements, I/O performance, and network bandwidth usage. Consider both short-term fluctuations and long-term usage patterns to accurately forecast future needs and potential bottlenecks. Collect and analyze performance metrics over extended periods, including peak and off-peak hours, to gain a comprehensive understanding of server behavior. Use appropriate monitoring tools to gather detailed data on query execution times, resource utilization patterns, and system responsiveness under varying loads. Identify recurring performance issues, resource contention points, or capacity limitations that may affect the overall efficiency of the PostgreSQL environment. Based on this analysis, develop actionable recommendations...

PostgreSQL - Architecture

When you start PostgreSQL`s instance (via pg_ctl start) the main process of the databases is started. In the past versions it was known as the postmaster process and now days it is called postgres. When this processes is started it allocates memory for two main things : shared memory and other background processes. The shared memory is a memory that the database uses mainly for cashing. Its main goal is to reduce the I/O operations and as a result of that improve the performance of the database. The other processes that the postgres process starts are responsible for the ongoing work of the database.  PostgreSQL`s architecture is based on a client/server communication. In the server side there is a server process that manages the files, database operations, connection and so on.. On the client side there is a tool/program which can be a graphical tool (gui)  like pgAdmin or a command line tool(cli) like psql  that is repnsible for connection the database and all...