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

Post a Comment

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...
1 comment
Read more

Job scheduler for PostgreSQL "pg_cron"

What is pg_cron   : -   pg_cron is a simple cron-based job scheduler for   PostgreSQL (9.5 or higher)   that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database . Why We need it ? Running periodic maintenance jobs or removing old data is a common requirement in PostgreSQL. A simple way to achieve this is to configure cron or another external daemon to periodically connect to the database and run a command. Let's see how it's works  Step 1 :-  For implementing/Installation of pg_cron you need to download source code from git Dowload link  export PATH=/usr/local/pgsql/bin:$PATH wget https://github.com/citusdata/pg_cron/archive/master.zip unzip master cd pg_cron-master/ make make install    Step 2 : - To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to  shared_preload_libraries   in post...
Post a Comment
Read more

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...
Post a Comment
Read more