wget https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/example-guided-project/flights_RUSSIA_small.sql
# RESPONSE
Resolving cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)... 169.63.118.104, 169.63.118.104
Connecting to cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud (cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud)|169.63.118.104|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 103865229 (99M) [application/x-sql]
Saving to: 'flights_RUSSIA_small.sql'
flights_RUSSIA 100% 99.05M 34.0MB/s in 2.9s
2024-10-01 16:17:25 (34.0 MB/s) - 'flights_RUSSIA_small.sql' saved [103865229/103865229]PostgreSQL Backup & Restore
In this project we’ll:
- Restore a full database from a backup
- Update a database and perform a full backup
- Drop a database and then restore it
Data
We will use a database from https://postgrespro.com/education/demodb distributed under the PostgreSQL licence. It stores a month of data about airline flights in Russia and is organized according to the following schema:
Restore db from Backup
Download Backup
- The file we will download is a full database backup of a month of flight data in Russia
- Now, you can perform a full restoration of the dataset by first opening the PostgreSQL CLI.
Connect PostgresSQL
Open a new PostgreSQL CLI
Restore from Script
\i <file_name>.Is the filename of the file you downloaded,flights_RUSSIA_small.sql.- This will restore the data into a new database called
demo
postgres=# \i flights_RUSSIA_small.sqlList Tables
- As you see now the prompt has changed to the name of the database created demo
demo=# \dt
List of relations
Schema | Name | Type | Owner
----------+-----------------+-------+----------
bookings | aircrafts_data | table | postgres
bookings | airports_data | table | postgres
bookings | boarding_passes | table | postgres
bookings | bookings | table | postgres
bookings | flights | table | postgres
bookings | seats | table | postgres
bookings | ticket_flights | table | postgres
bookings | tickets | table | postgres
(8 rows)Modify db & Backup
Review Data
Let’s review the contents of the table aircrafts_data
demo=# SELECT * FROM aircrafts_data;
aircraft_code | model | range
---------------+-------------------------------+-------
773 | {"en": "Boeing 777-300"} | 11100
763 | {"en": "Boeing 767-300"} | 7900
SU9 | {"en": "Sukhoi Superjet-100"} | 3000
320 | {"en": "Airbus A320-200"} | 5700
321 | {"en": "Airbus A321-200"} | 5600
319 | {"en": "Airbus A319-100"} | 6700
733 | {"en": "Boeing 737-300"} | 4200
CN1 | {"en": "Cessna 208 Caravan"} | 1200
CR2 | {"en": "Bombardier CRJ-200"} | 2700
(9 rows)Modify Data
- Suppose a new model of aircraft is being added to the fleet, and you, as the database administrator, are responsible for updating the database to reflect this addition.
- The aircraft they wish to add is the Airbus A380, which has a range of 15,700 km and aircraft code “380”.
demo=# INSERT INTO aircrafts_data(aircraft_code, model, range) VALUES (380, '{"en": "Airbus A380-800"}', 15700);
INSERT 0 1Review Alteration
demo=# SELECT * FROM aircrafts_data;
aircraft_code | model | range
---------------+-------------------------------+-------
773 | {"en": "Boeing 777-300"} | 11100
763 | {"en": "Boeing 767-300"} | 7900
SU9 | {"en": "Sukhoi Superjet-100"} | 3000
320 | {"en": "Airbus A320-200"} | 5700
321 | {"en": "Airbus A321-200"} | 5600
319 | {"en": "Airbus A319-100"} | 6700
733 | {"en": "Boeing 737-300"} | 4200
CN1 | {"en": "Cessna 208 Caravan"} | 1200
CR2 | {"en": "Bombardier CRJ-200"} | 2700
380 | {"en": "Airbus A380-800"} | 15700
(10 rows)Backup Db
Similar to MySQL, first exit PostgreSQL before processing the backup
demo=# \q
theia@theiadocker-emhrcf:/home/project$ To execute the backup we’ll do it from within the phAdmin
- Open phAdmin
- Enter password when prompted
- Servers (from left menu)
- Scroll down to demo
- Right Click > Backup
- Click on the folder in the far right
- Scroll down to an unlocked directory > create the file: /var/lib/pgadmin/
- Enter name for the backup: demo_backup
- Set format = tar
- Backup
Restore Full Backup
Let’s say the db is deleted, corrupted, hacked. Let’s restore from the backup we just created
Delete db
From the file tree, right click demo, delete
Create db
- Right click on Databases
- Create db
- Name it: restored_demo
- Save
Restore from Backup
- Find restored_demo from the file tree
- Make sure All Files is chosen in the lower corner
- Select
- Format: Custom to tar
- Restore
Let’s go back to the CLI and check to see if the data is all there
Connect to db
postgres=# \connect restored_demo
psql (14.13 (Ubuntu 14.13-0ubuntu0.22.04.1), server 13.2)
You are now connected to database "restored_demo" as user "postgres".
restored_demo=#Set Search Path
restored_demo=# SELECT pg_catalog.set_config('search_path', 'bookings', false);
set_config
------------
bookings
(1 row)List Tables
restored_demo=# \dt
List of relations
Schema | Name | Type | Owner
----------+-----------------+-------+----------
bookings | aircrafts_data | table | postgres
bookings | airports_data | table | postgres
bookings | boarding_passes | table | postgres
bookings | bookings | table | postgres
bookings | flights | table | postgres
bookings | seats | table | postgres
bookings | ticket_flights | table | postgres
bookings | tickets | table | postgres
(8 rows)Review Data
restored_demo=# SELECT * FROM aircrafts_data;
aircraft_code | model | range
---------------+-------------------------------+-------
773 | {"en": "Boeing 777-300"} | 11100
763 | {"en": "Boeing 767-300"} | 7900
SU9 | {"en": "Sukhoi Superjet-100"} | 3000
320 | {"en": "Airbus A320-200"} | 5700
321 | {"en": "Airbus A321-200"} | 5600
319 | {"en": "Airbus A319-100"} | 6700
733 | {"en": "Boeing 737-300"} | 4200
CN1 | {"en": "Cessna 208 Caravan"} | 1200
CR2 | {"en": "Bombardier CRJ-200"} | 2700
380 | {"en": "Airbus A380-800"} | 15700
(10 rows)restored_demo=#SELECT * FROM tickets WHERE book_ref = '0002D8';
ticket_no | book_ref | passenger_id | passenger_name | contact_data
---------------+----------+--------------+-----------------+----------------------------------------------------------------------
0005435767874 | 0002D8 | 2126 190814 | SANIYA KOROLEVA | {"email": "s_koroleva_1965@postgrespro.ru", "phone": "+70635878668"}Modify Data
Suppose we need to correct a first name from “Saniya” to “Sanya”, so we need to edit the passenger_name
restored_demo=# UPDATE tickets SET passenger_name = 'SANYA KORELEVA' WHERE book_ref = '0002D8';
UPDATE 1
# Verify using the same command as above - and you see the name has been changed
restored_demo=#SELECT * FROM tickets WHERE book_ref = '0002D8';
ticket_no | book_ref | passenger_id | passenger_name | contact_data
---------------+----------+--------------+----------------+----------------------------------------------------------------------
0005435767874 | 0002D8 | 2126 190814 | SANYA KORELEVA | {"email": "s_koroleva_1965@postgrespro.ru", "phone": "+70635878668"}
(1 row)
\q #to get outClose Connection
restored_demo=# \qFull Backup
Similar to the way we did this using pgAdmin, we can do it in the CLI
- Let’s say many other changes were performed, more bookings, flights scheduled….
- Let’s create a bull backup and will name it restored_demo_backup.sql
restored_demo=# pg_dump --username=postgres --host=localhost restored_demo > restored_demo_backup.sql