Clota Blog

Category: PostgreSQL

  • PostgreSQL Backup

        pg_basebackup is used to take base backups of a running PostgreSQL database cluster. We can take hot-backup and the backup can be used for…    1.     point-in-time recovery as the starting point for a log shipping or     2.     streaming replication standby servers       It makes a binary copy of the database cluster files, while making sure the system is…

  • Streaming Replication Step by step notes

    CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD ‘******’; echo “host replication replicator 192.168.0.126/32 md5” >> pg_hba.conf \ /u01/app/postgres/bin/psql -c “select pg_reload_conf()” /u01/app/postgres/pgsql/bin/pg_basebackup -h 192.168.0.117 -U replicator -p 5432 -D /u05/pgsql/database -Fp -Xs -P -R NOTE:- In my case, I have installed PostgreSQL (PostgreSQL 12) in /u01/app/postgres/pgsql/bin/ directory and data directory  is /u05/pgsql/database 

  • CAP Theorem

    In theoretical computer science, the CAP theorem, also named Brewer’s theorem after computer scientist Eric Brewer, states that it is impossible for a distributed data store to simultaneously provide more than two out of the following three guarantees: Consistency Every read receives the most recent write or an error. Availability Every request receives a (non-error)…

  • Important SQL Server Scripts…

    Restore Progress Check… ========================================================================== DECLARE @DBName VARCHAR(64) = ‘ODS’ DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery of database’, @DBName INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery completed’, @DBName SELECT TOP 1     @DBName AS [DBName]    ,[LogDate]    ,CASE       WHEN…

  • PG Basic Commands

    ========================================= –DB size select pg_database_size(‘databaseName’); /*  ?column? | pg_database_size ———-+——————     48104 |          7840623 */ postgres=# \l+ /*                                                                          List of databases       Name      |     Owner     | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description —————-+—————+———-+————-+————-+———————–+———+————+——————————————–  MyDB_Name       | MyDB_Name user | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 47 MB   | pg_default |  MyDB_Name tmpdb | MyDB_Name tmp  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 17 MB   | pg_default | */ =========================================…

  • PG Backup Dump

    Types of DB backup ·         Logical BACKUP  <pg_dump> ·         Physical Backup  PG SQL Dump     Three tasks we are going to do…         Restoring a backup     Performing a backup     Scheduling a backup job to run daily at 1am Create acweb Database createdb acweb Download TestDB from gitHub wget https://github.com/linuxacademy/content-postgresql-deepdive/raw/master/acweb/acweb.tar Restore the downloaded…

  • pg_switch_wal()

    pg_switch_wal() is a system function which forces PostgreSQL to switch to a new WAL file.     pg_switch_wal() returns the end LSN + 1 of the old WAL file.     However, if there has been no activity which generates WAL since the last WAL file switch, a switch will not be carried out and the start location of the current…

  • pg_restore

         pg_restore restores a PostgreSQL database from an archive file created by pg_dump.      pg_restore [connection-option…] [option…] [filename]      Example:      We have dumped a database called mydb into a custom-format dump file:      pg_dump -Fc mydb > db.dump      To drop the database and recreate it from the dump:…

  • Import CSV File Into PostgreSQL

     Create a Table in Postgres  CREATE TABLE persons ( id SERIAL, first_name VARCHAR(50), last_name VARCHAR(50), dob DATE, email VARCHAR(255), PRIMARY KEY (id) ); Create or Import csv file I am creating here… vi emp.csv First Name,Last Name, DOB,Email Abhishek, Yadav,18-Dec-1982,[email protected] Deepak, Varma,20-Jan-1982,[email protected] Connect PostgreSQL and execute below command to import the csv file into persons…