Postgresql Restore Command Reference

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, or tar formats (auto-detected).
  • -C (optional): include CREATE DATABASE in the restore script.
  • -c / --clean: drop existing objects before recreating them.
  • -j <n>: run parallel restore jobs (only for custom or directory formats).
  • -O / --no-owner: skip setting original object owners.
  • -e / --exit-on-error: stop on first error.

Example: drop & recreate DB then restore:

dropdb edbstore
pg_restore -U postgres -C -d postgres edbstore_full_fc.dmp

4. Additional Options & Best Practices

  • --single-transaction (psql -1): wrap restores in one transaction to ensure all-or-nothing.
  • --no-owner (pg_restore -O): useful if you can’t connect as the original owner.
  • --exit-on-error (pg_restore -e, psql --set ON_ERROR_STOP=on): halt on first failure.
  • Parallel restores (pg_restore -j): speeds up large restores on multi-CPU machines.
  • Always restore new databases from template0 to avoid unwanted template1 objects.
  • After restore, run ANALYZE on large tables to update planner statistics.

Comments

Post a Comment

Popular posts from this blog

Oracle 19c Dataguard Installation with DG Broker

Oracle 19c Data Guard – Standby Creation, Switchover & Failover Oracle 19c Physical Standby Creation & Management (RMAN Duplicate + Data Guard Broker) This article walks through building a physical standby using RMAN  DUPLICATE FROM ACTIVE DATABASE , then managing switchover / failover with the Data Guard Broker. The commands were tested on Oracle Linux 8 with Oracle Database 19c . Assumptions Two hosts ( oracle1.localdomain & oracle2.localdomain ) with Oracle 19c installed. Primary instance runs on oracle1 ; standby will be created on oracle2 . Listener port 1521 is reachable in both directions (check firewalls). Primary DB name MYDBPROD ; standby unique name MYDBDG . 1 – Primary Server Preparation 1.1 Switch to ARCHIVELOG & Force Logging -- Check mode SELECT log_mode FROM v$database; -- If NOARCHIVELOG: SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCH...
Read more

Error when Installing Some Postgresql Packages (Perl IPC-Run)

Resolving PostgreSQL 17 Dependency Error on Oracle Linux 8 Fixing perl(IPC::Run) Dependency Error When Installing PostgreSQL 17 on Oracle Linux 8 Background While preparing a fresh Oracle Linux 8 server for a PostgreSQL 17 deployment, an attempt to install all PostgreSQL packages in one go failed with an unsatisfied dependency error: Problem 1: cannot install the best candidate for the job - nothing provides perl(IPC::Run) needed by postgresql17-devel-17.4-1PGDG.rhel8.x86_64 ... Root Cause The perl(IPC::Run) module (along with several tool‑chain libraries) resides in the CodeReady Builder repository, which is disabled by default on Oracle Linux 8. Because postgresql17-devel and postgresql17-test depend on that module, dnf / yum cannot resolve the full dependency tree unless the repository is enabled. CodeReady Builder hosts developer‑oriented packages—compilers, debuggers, Perl/Python modules—that ma...
Read more

Creating Jobs With Different Users via pg_cron in Azure Postgresql Flexible Server

PostgreSQL pg_cron: Scheduling Materialized View Refresh Across Databases PostgreSQL pg_cron: Scheduling Materialized View Refresh Across Databases In this tutorial, I’ll walk you through the process of scheduling a Materialized View refresh using the pg_cron extension in PostgreSQL. Specifically, we’ll cover how to execute a scheduled SQL command in a different database using cron.schedule_in_database . Step 1: Enable the pg_cron Extension in Azure Portal On Azure PostgreSQL Flexible Server, the pg_cron extension is managed directly through the Azure portal. Follow these steps: Go to the Azure portal and navigate to your PostgreSQL Flexible Server instance. Under the Server Parameters section, locate the azure.extensions parameter. Add PG_CRON to the list of allowed extensions (as shown in the screenshot). Once enabled, the extension is automatically installed in the default `postgres` database . If you need to schedule jobs in o...
Read more