Import Data into Light Cloud: Upload a .sql Dump or Stream It with psql

To import a SQL dump into a PostgreSQL database on Light Cloud, open the database in the console, go to the Backup tab, click Import dump and choose the file. Plain .sql and gzipped .sql.gz files both work. For large dumps, run the file with psql from your computer instead.

This is part 5 of the database series. It adds a publishers table to the bookshop-db database from part 1, first with the console and then with psql.

What you will do

• Import a small SQL file with Import dump.
• Check the new data with a query.
• Import a second file with psql, the way to go for large dumps.

Before you start

• The bookshop-db database from part 1.
• The two files from the repository: imports/publishers.sql and imports/more-books.sql. If you cloned it in part 1, run git pull in its folder. Otherwise, download the files from GitHub.
• For Step 4, psql from part 1.

Step 1: Look at the file

imports/publishers.sql is what a supplier might send you. It creates a table of three (made-up) publishers, adds a publisher_id column to books, and fills it in:

CREATE TABLE publishers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);

INSERT INTO publishers (name, city) VALUES
  ('Lighthouse Press', 'Edinburgh'),
  ('Northwind Books', 'Toronto'),
  ('Blue Harbour', 'Gdansk');

ALTER TABLE books ADD COLUMN publisher_id INTEGER REFERENCES publishers(id);

UPDATE books SET publisher_id = 1 WHERE author_id IN (1, 3);
UPDATE books SET publisher_id = 2 WHERE author_id = 2;
UPDATE books SET publisher_id = 3 WHERE author_id = 4;

Always read a dump before you import it: it runs with full access to your database.

Step 2: Import it

1. In the Light Cloud console, open bookshop-db and its Backup tab.
2. Click Import dump and choose publishers.sql.

The import starts as soon as you choose the file; there is no second confirmation. For this small file it finished within a few seconds, with the message Dump imported.

The import runs into the database as it is. Nothing is deleted first: tables the dump does not mention stay as they are, and a table that already exists is an error unless the dump drops it first.

Step 3: Check the result

Open the Data tab. The table list now has publishers. Ask how many books each publisher has:

SELECT p.name AS publisher, p.city, count(b.id) AS books
FROM publishers p
JOIN books b ON b.publisher_id = p.id
GROUP BY p.name, p.city
ORDER BY books DESC;

All 10 books have a publisher. The query box is explained in part 3.

Step 4: Import with psql

Import dump takes files up to 512 MB uncompressed, and the import has about four and a half minutes to finish. For bigger dumps, or to import from a script, run the file with psql and the DATABASE_URL from part 1:

$ psql "$DATABASE_URL" --single-transaction -v ON_ERROR_STOP=1 -f imports/more-books.sql
INSERT 0 2


PS> psql "$env:DATABASE_URL" --single-transaction -v ON_ERROR_STOP=1 -f imports/more-books.sql
INSERT 0 2

Two more books arrived. The two options make a failed import safe:

• -v ON_ERROR_STOP=1 stops at the first error, instead of carrying on with the rest of the file.
• --single-transaction runs the whole file as one transaction, so an error undoes everything the file already did. Your database is either fully imported or unchanged.

Import dump also stops at the first error, but keeps what ran before it. With psql, you choose.

What a dump may contain

The console and psql run the file with your database user's rights. That user owns your tables but is not a superuser, so:

• Plain SQL only. A .sql file, or one gzipped as .sql.gz. The console rejects pg_dump -Fc archives, tar and zip files with a message saying so. Restore a -Fc archive with pg_restore instead.
• No owners or permissions from another server. ALTER TABLE ... OWNER TO postgres fails with must be member of role "postgres", and GRANT ... TO some_user with role "some_user" does not exist. Create the dump with pg_dump --no-owner --no-privileges, like the console's own backups.
• No CREATE DATABASE. It fails with permission denied to create database. Dump a single database without -C, and import it into the Light Cloud database you created.

A backup downloaded from any Light Cloud database, as in part 4, meets all three rules and imports as it is.

Troubleshooting

Import failed: ERROR: relation "publishers" already exists

The dump creates a table the database already has, for example because you imported the same file twice. I got exactly this on a second run; since it was the first statement, nothing changed. Import into an empty database, or drop the table first.

must be member of role "postgres"

The dump sets owners. See What a dump may contain.

The import took too long

The file needs more than the console's time limit. Run it with psql as in Step 4.