What is the theory and workflow for taking a snapshot of a PostgreSQL database and restoring it in a running application?

Question

The only thing I could find to get started is: Is it possible to quickly create/restore database snapshots with PostgreSQL?

I also see things like this, but I'm not sure if that's all you need.

I am neither a DBA but a software developer with lots of Rails and Node.js experience, connecting to databases here and there but never really managing them.

What is the general process for creating a database snapshot in PostgreSQL, and restoring from the snapshot? Or if you are just cloning the database snapshot and starting fresh, just starting from the snapshot. The first case of restoring from a snapshot is if anything goes wrong, restoring. The second use case is giving someone else a database dump and having them load it up.

What I've started doing so far is:

import knex from 'initializers/knex'
import fs from 'fs'
import stringifyCSV from 'csv-stringify'

async function start() {
  const csvStream = fs.createWriteStream('backups/table_1.csv')
  const recordStream = knex.select('*').from('table_1')
    .stream()
  const stringifier = stringifyCSV({
    delimiter: ','
  })
  stringifier.on('readable', function(){
    let row;
    while(row = stringifier.read()){
      csvStream.write(row + '\n')
    }
  })
  stringifier.on('error', function(err){
    console.error(err.message)
  })
  stringifier.on('finish', function(){
    console.log('done')
  })

  let i = 0
  recordStream.on('data', data => {
    if (i === 0) {
      let headers = Object.keys(data)
      stringifier.write(headers)
    }
    stringifier.write(Object.values(data))
    i++
  })
  recordStream.on('end', () => stringifier.end())
}

module.exports = start

But I have a feeling this isn't the right approach, especially if you are trying to create a "snapshot" (in the middle of other application activity which reads from and writes to the database). How do you know where the database snapshot left off? How do you prevent writes during snapshot generation? (Or do you even do that?)

Basically, how do you create a snapshot, and restore from a snapshot, in PostgreSQL? If there's just a couple of bash script commands, then what are those I should run? If it's more theory that I need, what is the basic theory? If I could do it in Node.js, that would also be good to know, but mainly I am looking for the standard approach to this problem, which I assume is a solved problem.

• https://www.postgresql.org/docs/current/backup-dump.html

It says:

Dumps created by pg_dump are internally consistent, that is, the dump represents a snapshot of the database as of the time pg_dump begins running. pg_dump does not block other operations on the database while it is working. (Exceptions are those operations that need to operate with an exclusive lock, such as most forms of ALTER TABLE.)

How does this work? Can you recreate this in Node.js-land?

• https://www.postgresql.org/docs/current/app-pgdump.html

Answer 1

Your best option is to call pg_dump and pg_restore from your program.

Backups created with pg_dump are always consistent, even if there are concurrent data modifications, because the whole dump is running inside a single REPEATABLE READ READ ONLY transaction, which guarantees that it sees a consistent snapshot of the database.