Back up and restore a cluster

BigAnimal automatically and continuously saves backups of your clusters. You can restore your cluster to any point in the past after the initial backup on cluster creation. That functionality is included in the free trial as well. While that means you don't have to do anything to generate backups, it's always sensible to test your backups.

If you haven't already, create a cluster on BigAnimal. For the free trial, you can have only one active cluster. Since this example is a demonstration of recovering a cluster from backup, it doesn't matter whether you start a new cluster or just restore your current cluster to its current state.

We're going to add a database called "baseball," which we'll populate with some Major League Baseball statistics.

create user baseball with password 'baseball_pwd';
grant baseball to edb_admin;
create database baseball with owner baseball;

Now you can switch to your new (and empty) baseball database.

\c baseball

Normally we'd use log files to determine the moment in time that we want to restore. The free trial doesn't provide access to the logs, however. So that we have accurate timing of our activities, let's change the prompt to include a timestamp. For convenience, we'll use the (almost) ISO-8601 format that the restore command accepts.

\set PROMPT1 '%`date +"%Y-%m-%dT%H:%M:%S%z"` %/%R%# '

For this demonstration, we're just going to import batter data from the Baseball Databank, which is in CSV form. While it's easy to import the data using PostgreSQL's COPY command, we'll need to first define a table to put that data into. Most of the columns are integers, but there are a few strings to consider as well. You can copy and paste this command into your terminal.

CREATE TABLE batters (
                      id SERIAL,
                      playerid VARCHAR(9),
                      yearid INTEGER,
                      stint INTEGER,
                      teamid VARCHAR(3),
                      lgid VARCHAR(2),
                      g INTEGER,
                      ab INTEGER,
                      r INTEGER,
                      h INTEGER,
                      "2b" INTEGER,
                      "3b" INTEGER,
                      hr INTEGER,
                      rbi INTEGER,
                      sb INTEGER,
                      cs INTEGER,
                      bb INTEGER,
                      so INTEGER,
                      ibb INTEGER,
                      hbp INTEGER,
                      sh INTEGER,
                      sf INTEGER,
                      gidp INTEGER,
                      PRIMARY KEY (id)
);

Now we can populate the table from the internet using the most recent data.

\COPY batters(playerid,yearid,stint,teamid,lgid,g,ab,r,h,"2b","3b",hr,rbi,sb,cs,bb,so,ibb,hbp,sh,sf,gidp) FROM PROGRAM 'curl "https://raw.githubusercontent.com/chadwickbureau/baseballdatabank/master/core/Batting.csv"' DELIMITER ',' CSV HEADER

Just to prove there's data loaded, let's look at the home run leaders for the 1998 season.

SELECT playerid, yearid, teamid,
       rank() OVER (PARTITION BY yearid ORDER BY hr desc) hr_rank,
       hr
FROM batters
WHERE yearid = 1998
ORDER BY hr_rank LIMIT 5;

Suppose someone wanted to revise history a bit.

UPDATE batters 
SET hr=0 
where playerid = 'mcgwima01' AND yearid = 1998;

Note the time so we can restore that data later. Verify the data has been changed by rerunning the 1998 home run leader query. Now go ahead and drop the whole table.

DROP TABLE batters;

You can verify the table is gone by looking at the list of tables.

\dt

For the free trial, you're limited to one active cluster. Restoring a cluster creates a brand new cluster that's initialized with the cluster backup. Quit psql (\q), and delete the cluster from the command line.

biganimal delete-cluster --id p-xxxxxxxxxxx

Restore the cluster using the timestamp before the time you dropped the table.

biganimal restore-cluster --id p-xxxxxxxxxxx --from-deleted --restore-point 2022-03-31T02:39:40+0000

✔ New Name for Restored Cluster, Leave empty to continue with source cluster data: █
Restored Cluster Password: *************
Region: US East 1
Instance Type: r5.large(2vCPU, 16GB RAM)
Volume Type: General Purpose SSD (GP3)
Volume Properties: gp3 (1 Gi - 16384 Gi, 3000 IOPS, 125 MiB/s tput)
✔ Specify database configuration, for example "application_name=sample_app&array_nulls=true". Leave empty to continue with source cluster data: █
High Availability: Keep Existing
Networking: Keep Existing
Add IP Range to allow network traffic to your cluster from the public Internet: Keep Existing
Are you sure you want to Restore Cluster ? [y|n]: y
Restore Cluster operation is started
Cluster ID is "p-yyyyyyyyyy"
To check current state, run: biganimal show-clusters --id p-yyyyyyyyyy

Check the status of the new cluster from the command line.

biganimal show-clusters

Once provisioning is complete, you'll want to grab the new connection information. The hostname changed!

biganimal show-cluster-connection --id  p-yyyyyyyyyy

Log into the new cluster. Be sure to use the new hostname, and use the edb_admin password you provided when restoring the cluster.

Verify that the batters table was restored:

select playerid, yearid, teamid,
       rank() OVER (PARTITION BY yearid ORDER BY hr desc) hr_rank,
       hr
from batters
where yearid = 1998
order by hr_rank limit 5;

Further reading

BigAnimal CLI reference and Backing up and restoring in the full version documentation.

• On this page
• Further reading