# Back up your Postgres database as SQL

> Run vibiz export --db to export your entire Postgres database as a compressed SQL snapshot ready for psql or save tables to CSV.

**To back up your Postgres database as a plain SQL dump, run `vibiz export --db` in your project folder or trigger an export from the project Export page in the console.** This generates a compressed SQL snapshot (`<slug>-<env>-database.sql.gz`) containing your schemas, tables, every row, keys, indexes, views, and functions. Because it omits role grants and ownership assignments, you can restore it into any standard Postgres database using `psql`.

Database backups give you portability and insurance. You might want an offsite snapshot before running an ambitious migration, a local copy of your schema and data for debugging, or a straightforward path to migrate between providers. This guide covers how to create a SQL dump from the CLI, how to export tables to CSV, how to restore the dump, and how environment permissions affect production data exports.

## How to back up your database using the CLI

You can generate a database snapshot with one command using the CLI. By default, running `vibiz export` without flags packages your project's source code, deployment guide, and database together. If you only want the SQL dump, add the `--db` flag:

```bash
# Export the database snapshot only
vibiz export --db

# Export the database snapshot along with individual CSV files for each table
vibiz export --db --csv
```

When you run this command, the CLI contacts the export service, queues the snapshot, and downloads the compressed archive into your current working directory. 

By default, an owner holding a full-access key exports the `production` database. If you want to dump the schema of your development environment instead, pass the `--env` flag:

```bash
vibiz export --db --env development
```

The output file uses the naming pattern `<slug>-<env>-database.sql.gz`. For example, a project named `shop` running in production produces `shop-production-database.sql.gz`. If you also passed the `--csv` flag, you will receive an accompanying `<slug>-<env>-database-csv.zip` file containing every table formatted as a CSV with header rows.

For details on managing connection strings and environments before backing up, see [Get a Postgres DATABASE_URL in one command](https://thelastapi.dev/blog/get-database-url-one-command).

## What is included inside the SQL snapshot

The export process creates a single, consistent snapshot of your Postgres database. It does not simply dump raw table contents; it structures the entire schema and state so that target databases can ingest it cleanly.

| Component | Included in dump | Notes |
| --- | --- | --- |
| Table structures and schemas | Yes | All `CREATE TABLE` definitions and schemas |
| Row data | Yes | Full `INSERT` or `COPY` data for every row |
| Primary and foreign keys | Yes | Constraints are defined and reapplied |
| Indexes | Yes | Secondary and unique indexes |
| Views and functions | Yes | Custom views, stored procedures, and triggers |
| Sequence positions | Yes | Auto-increment sequence values stay intact |
| Roles, owners, and grants | No | Excluded so imports work on any Postgres role |

Because table ownership and role grants are deliberately excluded from the SQL statements, you do not need matching Postgres users or superuser privileges on your target database. Any database user that has permission to create tables in the target database can restore the entire snapshot.

You can also read schema tables and run ad-hoc read queries right from the CLI or API without doing a full export. See the [Database documentation](https://thelastapi.dev/docs/database) for details on querying database state over HTTP.

## How to restore your SQL backup into Postgres

You can restore your `.sql.gz` dump into any empty Postgres database running version 15 or newer. Because the file is gzipped, pipe `gunzip` directly into `psql` using your connection string:

```bash
gunzip -c shop-production-database.sql.gz | psql "$NEW_DATABASE_URL"
```

If you are restoring to a local Postgres instance running on your laptop, the command works the same way:

```bash
gunzip -c shop-production-database.sql.gz | psql -U postgres -d my_local_db
```

Because the SQL file defines table schemas, functions, and sequence counters in order, `psql` will reconstruct the database automatically. If you are integrating Postgres into modern application stacks, check our guide on how to [Use Postgres from Next.js with Drizzle or Prisma](https://thelastapi.dev/blog/postgres-nextjs-drizzle-prisma).

## Access controls and protecting production rows

When backing up databases, secret management and access boundaries are critical. Keys on thelastapi fall into two categories: **Full access** (held by owners and CI) and **Limited keys** (held by AI agents and restricted environments).

Limited keys cannot export the production database. If an AI agent or a Limited key attempts to run an export against production, the API rejects the request:

```text
403 forbidden (owner_only)
```

By default, an export initiated by a Limited key defaults to the `development` environment. On thelastapi, the development database branch includes production tables and migration ledgers (such as Prisma or Drizzle migration state) but **none of production's rows**. Every table in development is emptied and verified before being handed out. This prevents test runs and agents from viewing customer data. 

To learn more about granting fine-grained access without handing over full account control, refer to [Keys and environments](https://thelastapi.dev/docs/keys). If a Limited key needs to pull production data, an owner must explicitly grant the `database.production:read` or `exports.production:read` permission.

Furthermore, database passwords and environment variables are **never included** inside the exported archive or SQL dump. If an account owner needs to back up environment variables alongside the database, they run:

```bash
vibiz export --secrets
```

This triggers an explicit confirmation prompt in the terminal and downloads a separate `.env` file containing secret values.

## Running exports over the HTTP API

You can also automate your backup workflows using HTTP calls or automation scripts. Backups can be requested directly from the export endpoints.

To trigger an export via curl:

```bash
curl -X POST https://thelastapi.dev/v1/projects/prj_123/exports \
  -H "Authorization: Bearer $VIBIZ_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"include":["database","database_csv"],"env":"production"}'
```

The endpoint responds with a `202 Accepted` status and an export object:

```json
{
  "object": "project_export",
  "id": "exp_01k123abc",
  "environment": "production",
  "include": ["database", "database_csv"],
  "status": "queued"
}
```

You can poll the status by querying the export ID:

```bash
curl https://thelastapi.dev/v1/projects/prj_123/exports/exp_01k123abc \
  -H "Authorization: Bearer $VIBIZ_API_KEY"
```

Once `status` changes to `"ready"`, the `files` array provides pre-signed download URLs for your SQL archive and CSV bundles. These signed download links remain valid for **one day** (24 hours). After 24 hours, the files are deleted from storage and marked as `expired`.

## Backup limits and large databases

The built-in export system is designed to handle common projects and side-project workloads easily, but there are operational limits to note:

- **Database file limit**: The export feature supports databases up to 2 GB compressed.
- **Code file limit**: Up to 1 GB compressed.
- **Concurrency**: Only one export can run at a time per project. Submitting a second export while one is running returns a `409 conflict` error containing the active `export_id`.
- **Frequency**: Projects are limited to 10 exports per day. Exceeding this returns a `429 rate_limited` error.

If your database exceeds the 2 GB compressed limit, you do not need to use the export interface. Because thelastapi provides standard serverless Postgres over the internet, you can use standard Postgres tooling like `pg_dump` directly against your live database. 

Fetch your production connection string by downloading your environment or reading your variables with an owner key, then run:

```bash
pg_dump "$DATABASE_URL" --no-owner --no-privileges | gzip > backup.sql.gz
```

For more details on resource architectures, read our overview of [Serverless Postgres for side projects: one database per project](https://thelastapi.dev/blog/postgres-database-per-project) and review the complete [Export documentation](https://thelastapi.dev/docs/export).

If you are ready to secure a copy of your database, run `vibiz export --db` in your terminal today, or visit the Export tab in your project console.

## Frequently asked questions

### How long are the download links for my database export valid?

Download links generated by an export remain active for 24 hours (one day). After that, the download links expire and the files are deleted from export storage, requiring you to initiate a new export.

### Can an AI agent or Limited API key export my production database?

No, Limited keys are restricted from accessing production rows and cannot export the production database (`403 owner_only`). By default, an export initiated by a Limited key only backs up the development branch, which contains schema tables but no production data rows.

### What should I do if my compressed Postgres database exceeds 2 GB?

The built-in export tool caps database snapshots at 2 GB compressed; if your database is larger, run `pg_dump` directly against your project's production `DATABASE_URL` using standard flags like `--no-owner --no-privileges`.

### What Postgres version is required to restore the SQL backup file?

You can restore the compressed `.sql.gz` dump into any empty Postgres database running version 15 or newer by unzipping the archive and piping it into `psql`.

---

Published 2026-11-15 · https://thelastapi.dev/blog/backup-postgres-as-sql
