Create a Postgres database, branch it and change the branch while the source stays the same
You will create a Postgres database, add a few rows and branch it. The branch is a full copy of the data with its own connection string, so you can change it while the source keeps running.
Prerequisites
You need:
- The
neviaCLI, installed and signed in. - A selected workspace where you have the admin or editor role, with
the names
ordersandorders-stagingfree. - The
psqlPostgres client.
To install psql:
# macOS
brew install libpq && brew link --force libpq
# Debian / Ubuntu
sudo apt install postgresql-clientCreate a database
Version, size and storage take platform defaults, so the command needs only a name:
nevia pg new orders
nevia pg lsorders should be ready, with no pending transition. If creation is still
running, run nevia pg ls again.
Add a table and some rows
nevia pg connect orders --uri prints the postgres:// URI. Pass it to
psql with a table, four rows and a count:
psql "$(nevia pg connect orders --uri)" <<'SQL'
CREATE TABLE IF NOT EXISTS pings (id serial PRIMARY KEY, note text);
INSERT INTO pings (note) VALUES
('csv-note-one'), ('csv-note-two'), ('baseline-a'), ('baseline-b');
SELECT count(*) FROM pings;
SQLThe count is 4. The URI reaches the database directly over TLS. If the connection fails, see Create and connect to a database.
Branch the database
nevia pg branch orders -b orders-staging
nevia pg status orders-stagingWait until orders-staging is ready, with no pending transition. It starts
with the same four rows and its own password. Change the branch only:
psql "$(nevia pg connect orders-staging --uri)" <<'SQL'
INSERT INTO pings (note) VALUES ('branch-mutation');
UPDATE pings SET note = 'baseline-a (edited in branch)' WHERE note = 'baseline-a';
SQLpsql prints INSERT 0 1 and UPDATE 1.
Compare the two
Run the same query against both databases:
for db in orders orders-staging; do
psql "$(nevia pg connect "$db" --uri)" -c \
"SELECT count(*), array_agg(note ORDER BY id) FROM pings;"
doneThe source still has 4 rows. The branch has 5, with the new branch-mutation
row and baseline-a rewritten.
Clean up
Delete the branch and its data first, then the source:
nevia pg rm orders-staging --yes
nevia pg rm orders --yes
nevia pg lsNeither database should appear in the list. If deletion is still running, run
nevia pg ls again.
Next steps
- Create and connect to a database covers connection details and password resets.
- Branch a database covers what a branch copies and what it needs.
- Sleep, wake and delete a database covers lifecycle actions and protection.
- Create your first computer serves a page from a computer and branches it.