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…
//
-
PG Basic Commands
PG Basic Commands ============================================================================================================== Create a User postgres=# create user user_name with encrypted password ‘mypassword’; CREATE USER s2user WITH PASSWORD ‘***************’; CREATE USER s2user WITH PASSWORD ‘***************’ VALID UNTIL 2040-12-01;CREATE USER s2user SUPERUSER LOGIN PASSWORD ‘12345’;CREATE USER s2user SUPERUSER LOGIN PASSWORD ‘12345’; postgres=# grant all privileges on database sample_db to user_name; ============================================================================================================== Insert a huge Data…
//
-
Table Partitioning in PostgreSQL
Hello Guys, In this blog, I am going to explain you about table partitioning in PostgreSQL Table Partitioning in PostgreSQL Partitioning – Partitioning involves splitting large tables into smaller ones according to some attribute (like time ranges, regions, or even user ID groups) but can still be treated as one table from the application. This is a…
//