Restore a full database backup (postgresql)

[HassanAITBRIK]

Question

We are facing a severe problem of data deletion on the postgresql database. We urgently need to recover the missing data.

In the documentation Security & Compliance Measures (https://vercel.com/docs/security#data-backup) we can see:
Data backup
Vercel backs up its customers' data every hour, each backup is kept for 30 days and is replicated worldwide to withstand regional disasters. Automatic backups are performed without affecting the performance or availability of database operations.All backups are stored separately in a storage service. If a database instance is deleted, all associated backups are also automatically deleted. Backups are periodically tested by Vercel's engineering team.

The instance has not been deleted, so backup is possible.
How to restore your backup ? Is there any way to restore a backup with a merging strategy (merging current and old database) ?

[amyegan]

Hey @HassanAITBRIK. Vercel Postgres is still in beta and automated backup support is not currently available.

It is possible for you to manually export and to restore data if you have a backup available. You can find the steps for that process at https://neon.tech/docs/import/import-from-postgres

[HassanAITBRIK]

@amyegan We lost all our customers' data during the night of August 6th to 7th. We have no backups.
We were assured by "Security & Compliance Measures" that we would be able to restore our data, but in the end we were not.

Do you have any experts who can help?
In some cases, if the deletion was recent, file recovery tools may be able to recover deleted files from the underlying file system. Tools such as extundelet for example.

[abibbs]

@HassanAITBRIK I'm interested in using Postgres hosted by Vercel myself in production but worry about this.

Any update?

[amyegan]

Automated backup support is not currently available for Vercel Postgres Beta. However, you can use pg_dump for manual backups: https://vercel.com/docs/storage/vercel-postgres/faq#external-data-migration

[rewbs]

Do you have an ETA for when a backup/restore support will be available?

[MeonisRP]

This is a major problem actually. I was 100% sure to dive deep into Vercel postgres, however now I'll think twice 🤔

[arepp23]

Any ETA on this? Sort of unusable for a production app without this.

[rewbs]

FYI for anyone still considering using Vercel postgres: I implemented the recommended approach of regular pg_dumps as an interim safety net; unfortunately this increased my postgres data egress costs significantly. I contacted support hoping there might be some flexibility on pricing until we get built-in backups/PiTR, but it seems there isn't. Definitely something to consider if you are storing non-trivial amounts of data.

[jjim1982]

Hey @rewbs, may I ask how you went about this? Can it be done by a function hosted in my project, or must it be done from an external nodejs server? I tried to do it using the "spawn" child process, however Vercel throws the error below. Does this mean it's not possible to do on Vercel?

Uncaught Exception: Error: spawn pg_dump ENOENT at ChildProcess._handle.onexit (node:internal/child_process:284:19) at onErrorNT (node:internal/child_process:477:16) at process.processTicksAndRejections (node:internal/process/task_queues:82:21) { errno: -2, code: 'ENOENT', syscall: 'spawn pg_dump', path: 'pg_dump', spawnargs: [ '-Fc', '-v', '-d',

[taylor-lindores-reeves]

@amyegan when will Automated Backup support be enabled? Since Postgres is now generally available?

[abduncan]

I, too, am still waiting on backup support. Will have to run production workloads on AWS instead.

[MickL]

Neon provides database branching and Vercel cant even make daily backups of my production database?
On Clouflare it is possible to restore any minute of the last 30 days with zero configuration and no extra costs.

[corwinstephen]

Yeah this is honestly kind of embarrassing in 2024

[swarnava]

We don't have any ETA for this unfortunately.

[MickL]

Switching to Cloudflare

[jjim1982]

Would someone be so kind as to answer my (probably basic) related question below?

I tried to use the "spawn" child process to run pg_dump in a function hosted on Vercel, however Vercel throws the error below. Does this mean it's not possible to run pg_dump on Vercel?

Uncaught Exception: Error: spawn pg_dump ENOENT at ChildProcess._handle.onexit (node:internal/child_process:284:19) at onErrorNT (node:internal/child_process:477:16) at process.processTicksAndRejections (node:internal/process/task_queues:82:21) { errno: -2, code: 'ENOENT', syscall: 'spawn pg_dump', path: 'pg_dump', spawnargs: [ '-Fc', '-v', '-d',

[backstromjoel]

Here's an example of how you can run it as a GitHub action cron job until they officially support it.

name: Production Database Backup

on:
  schedule:
    - cron: '0 2 * * *'
  workflow_dispatch:

jobs:
  backup:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Set current date
        run: echo "CURRENT_DATE=$(date +%Y%m%d%H%M)" >> $GITHUB_ENV
      - name: Postgres Dump Backup
        uses: tj-actions/pg-dump@v3
        with:
          database_url: ${{ secrets.POSTGRES_URL }}
          postgresql_version: '15'
          path: 'backups/backup-${{ env.CURRENT_DATE }}.sql'
          options: '-O'
      - name: Upload backup
        uses: actions/upload-artifact@v4
        with:
          name: db-backup
          path: 'backups/backup-${{ env.CURRENT_DATE }}.sql'