PostgreSQL Logical Backup & Restore

2.Logical Backup

  1. dump backup  
  2. dumpall backup  //full backup

1.Dump Backup use pg_dump Utility:

su - postgres
cd /usr/pgsql-11/bin

pg_dump Options:
-h    hostname
-p    port number
-d    database name
-t     table name
-F    format
        p   plain format  (filename=filename.sql)
        c   custom format (filename=filename.dump or filename.dmp)
        t    tar format  (filename=filename.tar)
        d   directory format (filename=filename)

./pg_dump -h localhost -p 5432 -d postgres -t emp -Fp -f /backup/emp.sql

Dump Restore:

Plain format Restore:

./psql -h localhost -p 5432 -d db2 -f /backup/emp.sql     //plain

Custom, tar, directory format Restore:

./pg_restore -h localhost -p 5432 -d db2 -f /backup/emp.dump  //c,t,d

2.Dump All Backup use pg_dumpall Utility:

su - postgres
cd /usr/pgsql-11/bin

Backup Command:

./pg_dumpall -h 192.168.1.21 -p 5432 -f /backup/emp.sql

Restore Command:

./psql -h localhost -p 5433 -f /backup/emp.sql


Popular posts from this blog

Oracle Database 19C Performance Tunning - PART 1

Image
Advantages: 1. Improved Query Performance •    Optimized SQL execution plans lead to faster query response times. •    Reduces unnecessary full table scans and improves indexing strategies. •    Parallel execution tuning speeds up large data processing tasks. 2. Better Resource Utilization •    Efficient use of CPU, memory, disk I/O, and network resources. •    Reduces contention on Redo Logs, Undo Tablespaces, and Buffer Cache. •    Helps in load balancing across multiple instances in RAC (Real Application Clusters). 3. Increased System Scalability •    Ensures that the database can handle a growing number of users and transactions. •    Proper tuning allows scaling without degrading performance. •    Optimized parallel processing ensures better performance on multi-core servers. 4. Lower Infrastructure Costs •    Reduces the need for add...
Read more

Oracle RMAN Backup And Restore

Image
RMAN: (Oracle 8) RMAN (Recovery Manager) is a utility provided by Oracle Database to perform backup, restore, and recovery operations. It is a command line tool. Features of RMAN in Oracle 19c Comprehensive Backup Capabilities: Full and incremental backups. Block-level backups for efficient data storage. Archived redo log backups. Fast Recovery Area (FRA) integration for centralized backup storage. Efficient Recovery Options: Point-in-time recovery (PITR). Complete and incomplete recovery. Flashback database capabilities for quick undo of changes. Multitenant Database Support: RMAN fully supports container databases (CDBs) and pluggable databases (PDBs). Provides flexibility to back up and recover individual PDBs or entire CDBs. Automatic Space Management: Manages disk space in the FRA. Automatically deletes obsolete backups and archived logs. Data Deduplication and Compression: Backup optimization through block-level deduplication. Built-in compression algorithms to reduce storage req...
Read more

Oracle Patching Using Opatch Utility

Image
Opatch: It is a utility for patching Oracle software, specifically Oracle databases and related products. It is used to apply, roll back, and check Oracle patches, including interim patches, one-off patches, and bundle patches. Types of Patches: Patch Type Purpose Release Frequency Includes Security Fixes? Includes Bug Fixes? Includes New Features? One-off Patch Fix a specific issue As needed No Yes No PSU (Patch Set Update) Security + Bug fixes Quarterly Yes Yes Minimal CPU (Critical Patch Update) Security fixes only Quarterly Yes No No RU (Release Update) Security + Bug + Functional changes Quarterly Yes Yes Yes BP (Bundle Patch) Exadata/Windows patch...
Read more

Welcome to DBA Master – Database Tips, Tricks, and Tutorials

Welcome to DBA Master ! This blog is dedicated to all things related to database administration , SQL optimization , and performance tuning . Whether you're a beginner or a seasoned DBA, you'll find practical guides, troubleshooting tips, and real-world tutorials to help you work smarter with data. What to Expect: SQL performance tuning tips Indexing strategies Backup and recovery best practices High availability and replication techniques Database creation, configuration, and setup Monitoring queries and scripts for proactive performance management Migration guides across different database platforms Security essentials and best practices Recommended tools for DBAs Real-world error fixes and how to solve them Stay tuned — exciting content is coming soon. Feel free to bookmark and share: www.dbamaster.com ! Thanks for visiting!
Read more

Oracle 19c Database Software Installation in OEL8

Image
 Pre-requisites for OS level:            Set the static IP Address     Disable the Firewall (systemctl stop firewalld & systemctl disable firewalld)     set SELINUX=permissive on /etc/selinux/config  ##Need to restart the server use init 6 Oracle Installation Pre-requisites Methods     Automatic Setup     Manual Setup      Automatic requisites Setup: (avoid step 1 to step 5): dnf install -y oracle-database-preinstall-19c Install the dependencies: curl -o oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm dnf -y localinstall oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm Manual Setup: step 1: Add the karenl parameters and values vi /etc/sysctl.conf     fs.file-max = 6815744 kernel.sem = 250 32000 100 128 kernel....
Read more

PostgreSQL Triggers

Creating a Trigger in PostgreSQL PostgreSQL Triggers are database call-back functions, which are automatically performed/invoked when a specified database event occurs. The following are important points about PostgreSQL triggers − PostgreSQL trigger can be specified to fire Before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE or DELETE is attempted) After the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed) A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies. If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name. The BEFORE, AFTER or INSTEAD OF keyword determines when the trigger actions will be executed relative to the insertion, modif...
Read more

PostgreSQL Opensource Installation in RHEL or Oracle Linux(OEL) 8 and above

Image
 Types OF Installation Binary(Packages) Source RPM (Red hat Package Management) Notes: Once Installed the PostgreSQL set Password for postgres (OS Level) User. Login the root user and set password like passwd postgres Binary Installation:   (Online Installation – Internet Must) Step 1:   goto https://www.postgresql.org/ and click Download Choose Packages on Top Left Cornor Step 2:   Select Your Operating System. Choose Linux and select your Linux distribution( Redhat/centos ). and scroll down Step 3:   Select PostgreSQL Version. Example Choose 17 Step 4:   Select Linux Platform. Example select Red Hat enterprise, CentOS, Scientific or Oracle version 8 Step 5:   Select OS architecture Type. Example Select x86_64     Step 6:   Copy the Script and paste your terminal Example: # Install the repository RPM: sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm # Disab...
Read more

PostgreSQL Cursor

Image
A PostgreSQL database cursor is a read-only pointer that allows a program, regardless of the language used, to access the result set of a query. This conserves the free memory of the server or machine running the SQL commands when a result set contains a large number of rows. Using cursors to iterate over rows of data also offers more control over the SQL records returned and makes the whole process more efficient. First, declare a cursor. Next, open the cursor. Then, fetch rows from the result set into a target. After that, check if there is more row left to fetch. If yes, go to step 3, otherwise, go to step 5. Finally, close the cursor. he following is valid for the cursor: NEXT LAST PRIOR FIRST ABSOLUTE count RELATIVE count FORWARD BACKWARD Using DECLARE CURSOR and FETCH test=# BEGIN; BEGIN test=# DECLARE mycur CURSOR FOR SELECT * FROM t_large WHERE id > 0; DECLARE CURSOR test=# FETCH NEXT FROM mycur; test=# FETCH 4 FROM mycur; test=# COMMIT; COMMIT I...
Read more

PostgreSQL Procedures

Benefits of Using Stored Procedures: Transaction control allowing us to COMMIT and ROLLBACK inside procedures. Very helpful for Oracle to PostgreSQL migration, the new procedure functionality can be a significant time saver. As you can see there are a couple of similarities between CREATE FUNCTION and CREATE PROCEDURE so things should be really easy for most end users. In this syntax: First, specify the name of the stored procedure after the CREATE PROCEDURE clause. Next, define a parameter list which is similar to the parameter list of user-defined functions. Then, specify the programming language for the stored procedure such as PLpgSQL and SQL. After that, place the code in the body of the stored procedure after that AS keyword. Finally, use double dollar ($$) to end the stored procedure. CREATE TABLE accounts ( id INT GENERATED BY DEFAULT AS IDENTITY, name VARCHAR(100) NOT NULL, balance DEC(15,2) NOT NULL, PRIMARY KEY(id) ); INSERT INTO accounts(name,balance) V...
Read more

PostgreSQL User & Role Management

Users and Roles: PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.  Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role. Role Attributes: CREATEDB                          NOCREATEDB CREATEROLE                NOCREATEROLE LOGIN                           NOLOGIN SUPERUSER                  NOSUPERUSER ...
Read more