Clota Blog

Category: pg_basic

  • 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)…

  • 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…

  • ACID Properties of RDBMS

     Hii guys, I hope you are doing great, Today here I am going to explain you about the ACID properties of RDBMS and its types. Transaction- Any operation that reads from or writes to a database is referred to as a transaction in relational databases. A transaction is a logical unit of work that can…

  • PostgreSQL Basic Commands

    Basic Command Description psql -d database -U user -W Connects to a database under a specific user psql -h host -d database -U user -W Connect to a database that resides on another host psql -U user -h host “dbname=db sslmode=require” Use SSL mode for the connection \c dbname Switch connection to a new database…

  • Tablespace in PostgreSQL

    Tablespace In PostgreSQL, tablespaces enable administrators to specify places in the file system where files representing database objects can be kept. When generating database objects, a tablespace can be referred to by name once it has been created. By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at…