Back Up a Database: Download a SQL Dump and Restore It

To back up a PostgreSQL database on Light Cloud, open the database in the console, go to the Backup tab and click Download backup. You get a complete SQL dump as a .sql.gz file. To restore it, unzip it and run it with psql against an empty database. For scheduled backups, run pg_dump with the connection string instead.

This is part 4 of the database series. It backs up the bookshop-db database from part 1, then proves the backup works by restoring it into a second database.

What you will do

• Download a backup of bookshop-db.
• Look inside it.
• Restore it into a new database, bookshop-copy, and check the data.
• Make the same backup from the terminal with pg_dump.

Before you start

• The bookshop-db database from part 1.
• psql and pg_dump, installed with psql in part 1. Use a current version: I used 18.6.
• A Light Cloud account on the Lite plan or higher.

Step 1: Download the backup

Open bookshop-db , go to the Backup tab and click Download backup :

The console makes a fresh dump of the database and downloads it; for my small bookshop that took about a second. The file is named after the database and the time in UTC, for example bookshop-db-2026-09-25T15-41-43.sql.gz, and was 1.4 KB.

Open a terminal in the folder with the file, usually Downloads.

Step 2: Look inside

The file is gzip-compressed SQL. Unzip it and keep the original:

$ gunzip -k bookshop-db-*.sql.gz
$ head -n 8 bookshop-db-*.sql
--
-- PostgreSQL database dump
--

\restrict asCDvFHFw2eKvwY4n71bGzNGHJM0iLagWYa5QvYYBYmRpBkzvdW4DCtA642ddcH

-- Dumped from database version 15.18
-- Dumped by pg_dump version 16.15


# PowerShell has no gunzip, so extract the file with 7-Zip (www.7-zip.org):
# right-click it, choose 7-Zip, then Extract Here.
# Open the .sql file in Notepad. It starts with the same lines:
--
-- PostgreSQL database dump
--

\restrict asCDvFHFw2eKvwY4n71bGzNGHJM0iLagWYa5QvYYBYmRpBkzvdW4DCtA642ddcH

-- Dumped from database version 15.18
-- Dumped by pg_dump version 16.15

Your \restrict line has a different random key.

It is a plain SQL file made by pg_dump. Further down are CREATE TABLE statements for authors, books and reviews, their rows as COPY blocks, and the primary keys. It contains no owners or permissions, so it restores into any PostgreSQL database, not only the one it came from.

Step 3: Restore into a new database

A backup you have never restored is only a hope. Restore it into a second database to check it:

1. In the console, create a database named bookshop-copy, size Dev , as in part 1.
2. Copy its connection string from Credentials , add ?sslmode=require, and save it as COPY_URL:

$ export COPY_URL='postgresql://u_ ******:******** @bookshop-copy-yourworkspace.db.light-cloud.io:5432/db ******?sslmode=require'


PS> $env:COPY_URL = 'postgresql://u_ ******:******** @bookshop-copy-yourworkspace.db.light-cloud.io:5432/db ******?sslmode=require'

1. Run the SQL file against it:

$ psql "$COPY_URL" -f bookshop-db-*.sql
SET
SET
...
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
...
COPY 4
COPY 10
COPY 1
...
ALTER TABLE


# Use your own file name after -f.
PS> psql "$env:COPY_URL" -f bookshop-db-2026-09-25T15-41-43.sql
SET
SET
...
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
...
COPY 4
COPY 10
COPY 1
...
ALTER TABLE

Output is trimmed where you see .... The COPY lines are the rows: 4 authors, 10 books and 1 review, everything from parts 1 to 3.

1. Check it:

$ psql "$COPY_URL" -c "SELECT count(*) AS books FROM books;"
 books
-------
    10
(1 row)


PS> psql "$env:COPY_URL" -c "SELECT count(*) AS books FROM books;"
 books
-------
    10
(1 row)

The copy is complete. The same file restores into PostgreSQL on your laptop: point psql at the local database instead of COPY_URL.

Step 4: Back up from the terminal

The Download backup button is right for a backup before a risky change. For regular backups, run pg_dump from a script or a scheduled job, with the DATABASE_URL of bookshop-db from part 1:

$ pg_dump "$DATABASE_URL" --no-owner --no-privileges -f bookshop.sql


PS> pg_dump "$env:DATABASE_URL" --no-owner --no-privileges -f bookshop.sql

It prints nothing when it works. --no-owner --no-privileges makes the same kind of portable file as the console. My bookshop.sql was 5 KB and said Dumped by pg_dump version 18.6. Add -Fc for PostgreSQL's compressed custom format, which you restore with pg_restore instead of psql.

Keep backups somewhere other than your laptop's Downloads folder, and remember they contain all your data.

Step 5: Delete the copy

bookshop-copy has done its job. Open it, go to Settings , and click Delete Database :

Type bookshop-copy to confirm and click Delete. Check the name twice: deleting cannot be undone, and part 5 still needs bookshop-db.

Troubleshooting

invalid command \restrict

Your psql is older than the pg_dump that made the file. Recent pg_dump versions add \restrict and \unrestrict lines as a safety guard. An older psql prints invalid command \restrict and invalid command \unrestrict but restores everything else; in my tests with psql 14.13 and 17.5 all rows arrived. Update psql to get rid of the messages.

relation "authors" already exists

The target database already has these tables. Restore into an empty database, or drop the tables first.