Sunbird RC (Registry & Credential)
  • Learn
    • Introduction
    • Sunbird RC Overview
      • Why do we need Sunbird RC?
      • Core Capabilities
      • Core Registry Verbs
      • Workflows
      • What Sunbird RC is and what it's not? (WIP)
      • Possibilities
      • Demo Links
    • Technical Overview
      • High level architecture
      • Technical Specification Draft
      • Tech Stack and Requirements
    • Adopters
    • Roadmap
  • Use
    • Getting started
      • Pre-requisites
      • Installation Guide
        • Registry CLI
          • Setup A Registry Instance
        • Manual installation through docker-compose
        • Production setup through Helm
    • Developer's Guide
      • Configurations
        • Frontend Configurations
        • Frontend - Proxy configuration
        • Audit Configuration
        • Notifications Configuration
        • View Templates Configuration
      • Schema Setup
        • Introduction To Schemas
        • Creating Your Own Schemas
        • Schema Configuration
        • Create Schemas With Custom Password
      • Setup the Backend
      • Setup the Frontend
      • Backup and Restore
        • PostgreSQL
          • SQL Dump
          • File System Level Backup
          • Continuous Archiving and Point-in-Time Recovery (PITR)
        • Cassandra
          • Snapshot-based backup method
          • Incremental backup method
          • Data Restore
      • Generic Identity And Access Management
      • Metrics
      • Custom Keycloak Build
      • Custom QR Code design
      • VC Verification Module
    • Integrations
      • SSO with existing systems
      • Digilocker Meripehchaan SSO
      • Digilocker Integration
    • Release Notes
      • Latest Release - v1.0.0
    • Admin Portal
      • Login
      • Get Started
        • Create Schema
        • Attestation Workflows (WIP)
        • VC Template
          • Custom VC Template (WIP)
        • Ownership (WIP)
        • Publish (WIP)
      • Dashboard
  • API Reference
    • Registry
      • Using The APIs
      • Create An Entity
      • Invite An Entity
      • Generate token
      • Generate admin token
      • Get An Entity
      • Get An Entity By Id
      • Update An Entity
      • Create A Property Of An Entity
      • Update A Property Of An Entity
      • Revoke a Credential
      • Delete An Entity
    • Schema
      • Create Schema
      • Get Schema
      • Update Schema
      • Delete Schema
      • Publish A Schema
    • Attestation API
      • Raise An Attestation
      • Get Attestation Certificate
    • Claims API
      • Get All Claims
      • Get Claim by ID
      • Attest A Claim
    • Discovery API
      • Search An Entity
    • File Storage API
      • Upload A File
      • Get Uploaded File
      • Delete A File/ Multiple Files
    • Bulk Issuance API
      • Get Sample Template
      • Upload CSV
      • Get all uploaded Files
      • Download a Report File
    • Metrics APIs
      • Get Count
      • Get Aggregates
    • Other APIs
      • Sign API
      • Verify API
      • Swagger JSON API
      • Health API
  • Reference Solutions for Functional Registries
    • Education
      • Education Ecosystem
        • Installation
      • Education Registries
        • Installation
    • Health Registries
      • Organ Registries
        • Frontend Setup
        • Backend Setup
        • User Guide
      • Health Facility Registry
    • Govt to Person (G2P)
  • Reference Solution for Digital Credentials
    • Certificate Issuance
      • Installation(WIP)
      • User Guide
    • Vaccination Platform
    • Unified Learners Passport (ULP)
      • ULP Capabilities
      • Example Scenario
      • Technical Components (WIP)
      • Demo/Sandbox Links (WIP)
      • Installation Guide (WIP)
        • Frontend Setup
        • Installation through docker-compose
        • Dummy records setup for refrence
    • eLocker
      • High Level Diagram
      • Installation (WIP)
        • Frontend Setup E-locker
      • User Guide
    • Skills & Work Credentials
  • Links
    • Source Code
    • Releases & Changelogs
    • Website
    • Roadmap
    • Reference links
    • Design
  • Community
    • Discussion Forum
    • Contributors
    • Contributing
    • Contribution Guidebook
    • Code of Conduct
    • Community Events
    • Status By Track
  • HELP
    • Roadmap
    • FAQs
    • Glossary
    • Guide to Electronic Registries and Verifiable Credentials
      • Verifiable Credentials
        • What issues will Verifiable Credentials address?
        • What are the key roles in Verifiable Credentials?
        • What are the components of Verifiable Credentials?
        • What are the benefits of Verifiable Credentials?
        • Digital Credentials vs Verifiable Credentials
        • QR code vs Verifiable QR code
        • Use Cases
      • Electronic Registries
        • Evolution of Electronic Registries
        • What issues will Electronic Registries address?
        • Benefits of Electronic Registries
        • Registry vs Database
        • Design Principles
        • Use Cases
      • Leveraging Existing data stores
    • External Open Source Software Attributions
Powered by GitBook
On this page
  • Create a SQL Dump
  • Restore from a dump file
  • Important Note

Was this helpful?

Edit on GitHub
  1. Use
  2. Developer's Guide
  3. Backup and Restore
  4. PostgreSQL

SQL Dump

Create a SQL Dump

Command:

pg_dump dbname > dumpfile

pg_dump is a PostgreSQL utility used for backing up databases. It generates an SQL file that can be used to recreate the database in its original state. The output can be redirected to a file or used in other formats for more control. pg_dump can be executed from a remote host with database access, but it requires read permissions for the tables being backed up. It offers advantages such as compatibility with different PostgreSQL versions and support for transferring databases across different machine architectures. Dumps created by pg_dump are internally consistent, representing a snapshot of the database at the time of the dump.

Restore from a dump file

Command:

psql dbname < dumpfile

When using pg_dump to generate a database dump, the output is saved in a file called "dumpfile." It's important to create the database specified by "dbname" separately before using the psql utility for restoration. Ensure that all relevant users exist before restoring the SQL dump to maintain ownership and permissions. By default, psql continues executing even after encountering SQL errors, but you can change this behavior by setting the ON_ERROR_STOP variable. Alternatively, you can restore the entire dump as a single transaction using the --single-transaction option. The use of pipes enables direct database transfer between servers using pg_dump and psql commands.

You might wish to run psql with the ON_ERROR_STOP variable set to alter that behavior and have psql exit with an exit status of 3 if an SQL error occurs:

psql --set ON_ERROR_STOP=on dbname < dumpfile

The ability of pg_dump and psql to write to or read from pipes makes it possible to dump a database directly from one server to another, for example:

pg_dump -h host1 dbname | psql -h host2 dbname

Important Note

When dealing with large pg_dump output files, certain operating systems may impose file size limits. Thankfully, pg_dump provides options to overcome this issue. Here are several methods to consider:

  1. Compressed Dumps: Utilize compression tools like gzip to compress the pg_dump output file. For example:

    • Create a compressed dump: pg_dump dbname | gzip > filename.gz

    • Restore the dump: gunzip -c filename.gz | psql dbname or cat filename.gz | gunzip | psql dbname

  2. Splitting Output: Use the split command to divide the output into smaller files that fit within the file system limits. For example:

    • Split into 2 GB chunks: pg_dump dbname | split -b 2G - filename

    • Restore the dump: cat filename* | psql dbname

  3. Custom Dump Format: If PostgreSQL was built with the zlib compression library, you can utilize the custom dump format, which compresses data as it writes to the output file. This format allows selective table restoration. For example:

    • Create a custom-format dump: pg_dump -Fc dbname > filename

    • Restore the dump: pg_restore -d dbname filename

Note: Custom-format dumps must be restored using pg_restore, not psql.

For very large databases, combining the split method with other approaches may be necessary.

  1. Parallel Dump: To expedite the dump process for large databases, you can use pg_dump's parallel mode, which dumps multiple tables simultaneously. Control the degree of parallelism using the -j parameter. Parallel dumps are supported for the "directory" archive format. For example:

    • Dump in parallel: pg_dump -j num -F d -f out.dir dbname

    • Restore in parallel: Use pg_restore -j to restore a parallel dump, regardless of the archive mode used.

Refer to the pg_dump and pg_restore reference pages for further details on these methods.

PreviousPostgreSQLNextFile System Level Backup

Last updated 1 year ago

Was this helpful?