DB Notes

PostgreSQL Restore Commands Reference PostgreSQL Restore Commands Reference This guide covers the three primary methods to restore PostgreSQL data: using psql for plain‐text dumps, pg_restore for archive-format dumps, and pg_dumpall for full-cluster plain‐text restores. Copy & paste these one-liner commands into your shell and adjust connection or file paths as needed. 1. Restore Plain‐Text Dump with psql Basic command: psql -U <user> -h <host> -p <port> -d <target_db> -f <dumpfile.sql> -d <target_db> : database to restore into. -f <dumpfile.sql> : path to the SQL dump. -1 (optional): wrap in a single transaction to abort on first error. 2. Restore Archive‐Format Dump with pg_restore Basic command: pg_restore -U <user> -h <host> -p <port> -d <target_db> <archive-file> -Fc / -Fd / -Ft : custom, directory...

Rescuing a Dropped PostgreSQL Database: Two pgBackRest Paths to Success That sinking feeling when a DROP DATABASE command slips through... we've all been there, or at least dreaded it. The good news is that with robust tools like PostgreSQL and pgBackRest, recovery is often very achievable. I recently put this to the test after "accidentally" dropping a database named NEWDB and wanted to share two distinct, successful methods I used to bring it back. The Setup: A PostgreSQL cluster with several databases, including one I created called NEWDB . Data was added to NEWDB . A full backup was taken using pgBackRest while NEWDB existed . Then, the fateful command: DROP DATABASE "NEWDB"; The mission: Restore NEWDB without impacting the other live databases. Method 1: The Classic & Controlled - Restore to Temporary, then pg_dump / pg_restore This is a well-trodden and highly reliable path for surgically extracting specific data f...

Cloning MYDBPROD → MYDBTEST with RMAN DUPLICATE Sometimes we need a quick, up‑to‑date copy of production for troubleshooting or functional testing. The fastest route is RMAN duplicate from active database , which streams the data files over the network—no backup & restore cycle required. Environment snapshot oracle1 → production server (SID MYDBPROD ) oracle2 → test server (SID MYDBTEST ) Database names: MYDBP (prod) → MYDBT (test) OS: Oracle Linux 8 SELinux Permissive , firewalld disabled. Only the Oracle binaries are installed on the test host—no database. 1  Configure the listener on the test server LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1) (SID_NAME = MYDBTEST) ) ) Start (or reload) it: lsnrctl start listener 2  Add tnsnames.ora en...

Practical RMAN Restore Guide Practical RMAN Restore Guide This guide shows how to restore and relocate an Oracle database via RMAN. We’ll assume you want all datafiles in a new folder ( /newpath instead of /oldpath ), and possibly handle redo logs in a new location as well. Steps include restoring SPFILE, cataloging backups, restoring archived logs, and point-in-time recovery. 1. Copy RMAN Backups to the New Server mkdir -p /u01/backup/test_restore cp /oldserver/backups/*.bkp /u01/backup/test_restore Copy all backup pieces (datafiles, control file, SPFILE, archivelogs) to a directory the new server can access. 2. Create Required Oracle Directories mkdir -p /u01/app/oracle/admin/MYDB/adump mkdir -p /u01/app/oracle/oradata/MYDB mkdir -p /u01/app/oracle/fast_recovery_area/MYDB mkdir -p /newpath/onlinelog Ensure directories exist for diagnostic files ( adump ), datafiles, FRA, and redo logs before proceeding. 3. Startu...

 Normally, you can't restore a single database from a cluster backup that are taken via pg_dumpall.In order to achieve that, you can follow the steps below: 1- Create a script that greps specific database portion of the dump: # ! / bin / bash [ $# - lt 2 ] && { echo "Usage: $0 <postgresql dump> <dbname>"; exit 1 ; } sed "/connect.*$2/,\$!d" $ 1 | sed "/PostgreSQL database dump complete/,\$d" 2- Extract the portion via example command below: sh onedbscript.sh full_dump.dmp MY_DATABASE > onlyonedatabase.dmp 3- You may restore and test if it worked if you want: psql -p <portnumber> -d MY_DATABASE < onlyonedatabase.dmp