Sunbird RC
v0.0.14
v0.0.14
  • Learn
    • Introduction
    • 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
    • Sunbird RC Overview
      • Features
      • Core Registry Verbs
      • Why do we need Sunbird RC?
      • What Sunbird RC is and what it's not? (WIP)
      • Core Capabilities
      • Technical Specification Draft
      • Workflows
      • High level architecture
    • Sunbird RC in action
      • Implementations (Work in Progress)
      • Possibilities
  • Use
    • Technical Requirements
    • Releases
    • Setup the Backend
    • Setup the Frontend
    • Leveraging Existing data stores
    • SSO with existing systems
      • Digilocker Meripehchaan SSO
  • Developer Documentation
    • Installation Guide
      • Registry CLI
        • Setup A Registry Instance
      • Manual installation through docker-compose
      • Production setup through Helm
    • Introduction To Schemas
    • Creating Your Own Schemas
    • Schema Configuration
    • Using The APIs
    • Create Schemas With Custom Password
    • Admin Portal
      • Login
      • Get Started
        • Create Schema
        • Attestation Workflows (WIP)
        • VC Template
          • Custom VC Template (WIP)
        • Ownership (WIP)
        • Publish (WIP)
      • Dashboard
    • Configuration
    • Developer Setup
    • VC Verification Module
    • Audit Configuration
    • Custom Keycloak Build
    • Metrics
    • Digilocker Integration
    • Custom QR Code design
    • Notifications Configuration
    • View Templates Configuration
    • Generic Identity And Access Management
    • 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
    • Frontend Configurations
    • Frontend - Proxy configuration
  • API Reference
    • Registry
      • 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
    • Education
      • Education Ecosystem
        • Installation
      • Education Registries
        • Installation
    • Certificate Issuance
      • Installation(WIP)
      • User Guide
    • eLocker
      • High Level Diagram
      • Installation (WIP)
        • Frontend Setup E-locker
      • User Guide
    • Health Registries
      • Organ Registries
        • Frontend Setup
        • Backend Setup
        • User Guide
    • Vaccination Platform
    • Skills & Work Credentials
    • Govt to Person (G2P)
    • 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
  • Links
    • Source Code
    • Releases & Changelogs
    • Website
    • Roadmap
    • Reference links
    • Design
  • Community
    • Discussion Forum
    • Contributing
    • Contributors
    • Contribution Guidebook
    • Code of Conduct
    • Community Events
    • Status By Track
  • HELP
    • Roadmap
    • FAQs
    • Glossary
Powered by GitBook

Copyright (c) 2023 EkStep Foundation under MIT License

On this page
  • Create a SQL Dump
  • Restore from a dump file
  • Important Note
  1. Developer Documentation
  2. Backup and Restore
  3. 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