Category: PostgreSQL
-
Azure Azure SQL Database Azure SQL Database Backup and Recovery Backup Tools for SQL Server DBA Worries Continued DBMS Concepts DBMS Concepts DBMS Concepts High Availability L1 DBA Monitoring My SQL Performance Tuning PostgreSQL Security SQL Scripting SQL Server
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…
//
-
Methods to change windows authentication to mixed mode (windows and SQL server authentication)
Hey guys, In this blog I am going to explain you about different methods to Enable Mixed Mode authentication Ø Enable Mixed mode authentication using Management studio Ø Enable Mixed mode authentication using Registry editor 1. Enable Mixed mode Authentication using MANAGEMENT STUDIO- STEP-1: Open a server in Management studio. Go to server -> right click -> instance ->properties…
//
-
Point-in-time recovery in SQL Server
Hey guys, In this blog we will discussed about point-in-time recovery in SQL Server and how to perform it and also we discussed on full, differential and transaction log backup. 1. Full backup – A Full backup is a complete backup of a database. The full backup contains all the data in a database and…
//
-
View in SQL Server
Hello guys, In this blog, I am going to explain you about a view and how to create a view inside the database. View– A VIEW in SQL Server is like a virtual table that contains data from one or multiple tables. It does not hold any data and does not exist physically in the…
//
-
Pages and Extent in SQL Server
Hey guys, In this blog I am going to explain you about Pages and Extent in SQL Server and also different types of pages in SQL Server Pages – A page is a unit of data storage in SQL. The size of a page is 8Kb. The data rows are put on the page serially after…
//
-
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…
//
-
SQL Server Network Configuration
Hey guys, hope you are doing great. In this blog I am going to explain you about SQL Server Network Configuration and enabling/disabling different protocols. SQL Server Network Configuration – SQL Server Network Configuration involves enabling the protocols that manage the connection to the SQL Server and configuring the available options for these network protocols. It…
//