The videos for PostgreSQL for MySQL Database Administrators (DBA) episodes three and four are live here and here. Episode three covers a simple backup and restoration while episode four covers some handy PSL commands. For those of you who missed the first two videos in this series you can find them here: Episode one and Episode two.
Many MySQL DBAs hear about PostgreSQL, and this is a guided introductory series on setting up and using PostgreSQL. The notes for the two latest episodes are below. Each of the videos in this series shows you steps and commands, and then shows a video of those commands being executed. If you are following along, these two episodes build on the previous ones.
Episode three
What we are going to do
- Backup a database
- Examine the backup
- Create a new database
- Load backup into new database
Creating a backup using pg_dump
$ pg_dump dvdrental > backup.sql
- pg_dump is the name of the program
- dvdrental is the name of the database to be backed up
- Dumping the output to file backup.sql
Create a new database
$ sudo su – postgres
$ psql
(psql 14.3 (Ubuntu 2:14.3-3-focal))
Type “help” for help.
dvdrental=# CREATE DATABASE newdvd;
dvdrental=# \q
$ ^d
Restoration
$ psql -d newdvd -f backup.sql
Episode four
What we are going to do
- Look at some PSQL commands
- Warn you about some PSQL commands
- Show you some PSQL commands
A quick summary
\c dbname Switch connection to a new database
\l List available databases
\dt List available tables
\d table_name Describe a table such as a column, type, modifiers of columns, etc.
\dn List all schemes of the currently connected database
\df List available functions in the current database
\dv List available views in the current database
\du List all users and their assigned roles
SELECT version(); Retrieve the current version of PostgreSQL server
\g Execute the last command again
\s Display command history
\s filename Save the command history to a file
\i filename Execute psql commands from a file
\? Know all available psql commands
\h Get help Eg:to get detailed information on ALTER TABLE statement use the \h ALTER TABLE
\e Edit command in your own editor
\a Switch from aligned to non-aligned column output
\H Switch the output to HTML format
\q Exit psql shell
Using \g To Repeat Commands
Using \c to switch databases
Using \d to see the Contents of a Database and More
Toggling Output Formats with \s and \H
Be sure to check out episode three and episode four of PostgreSQL for MySQL DBAs!
