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

All about pg_hba.conf(authentication methods- Postgresql)

  pg_hba.conf is the PostgreSQL access policy configuration file, which is located in the /var/lib/pgsql/10/data/ directory (PostgreSQL10) by default. The configuration file has 5 parameters, namely: TYPE (host type), DATABASE (database name), USER (user name), ADDRESS (IP address and mask), METHOD (encryption method) host all all 192.168.109.103/22 md5 host dbName user 192.168.109.106/22 trust Modify the server-side pg_hba.conf file Make the shell can connect to the postgres database secretly: Modify the authentication file $PGDATA/pg_hba.conf, add the following lines, and reload to make the configuration take effect immediately. host pankajconnect postgresql 192.168.8.103/32 trust Reload to take effect: pg_ctl reload -D $PGDATA Examples: 1. Allow local login to the database using PGAdmin3, database address  localhost, user user1, database user1db: host user1db user1 127.0.0.1/32 md5 2. Allow 10.1.1.0~10.1.1.255 network segments to log in to the database: host all all 10.1.1....
Post a Comment
Read more