Scribd
Upload
91 views12 pages

3.backup Restore Tools

The document discusses several PostgreSQL command-line client applications including psql for interacting with databases, createdb and dropdb for creating and dropping databases, pg_dump for backing up databases, pg_dumpall for backing up all databases, and pg_restore for restoring backups. It provides examples of basic usage and common commands for each tool.

Uploaded by

Tien Nguyen Manh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
91 views12 pages

3.backup Restore Tools

The document discusses several PostgreSQL command-line client applications including psql for interacting with databases, createdb and dropdb for creating and dropping databases, pg_dump for backing up databases, pg_dumpall for backing up all databases, and pg_restore for restoring backups. It provides examples of basic usage and common commands for each tool.

Uploaded by

Tien Nguyen Manh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 12

Other PostgreSQL client applications

Command-line tools

1
PostgreSQL Client Applications

2
PostgreSQL Client Applications
 Open Terminal
 cd C:\Program Files\Postgres\13\bin\
 Help: Program_name --help

3
PostgreSQL Client Applications
 Help: Program_name --help

4
PostgreSQL Client Applications
 pg_config: see the current configuration values
used to compile and install the PostgreSQL
package

pg_config --help
pg_config --version
Pg_config --bindir

5
PostgreSQL Client Applications
 psql: a command-line interface to the
PostgreSQL system
 Simple usage:
– Connect to the postgres (or test) database with postgres
(barney) account
psql postgres postgres
psql test barney
– Meta-command in psql
 to list all databases: \l
 to quit psql: \q
 Connect to other database: \c test
 View list of tables: \dt ; \dt store.*
6
 Show table schema: \dt store."Customer"
PostgreSQL Client Applications
 createdb: create a new database
createdb -U postgres test2

 dropdb: drop a database


dropdb -U postgres test2

7
PostgreSQL Client Applications
 pg_dump: dump (or back up) the contents of a
database on the PostgreSQL system to a file
– Script: SQL files
– Archived: compressed binary files (using pg_restore to
restore)
pg_dump -U postgres -f D:\testDB_bk.sql test
pg_dump -U postgres test >D:\testDB_bk.sql
pg_dump -U postgres -Fc test >D:\testDB_bk.dump
-F: output file format (custom (-Fc), directory (-Fd), tar (-Ft), plain (-Fp
default))
-U: login user
8 -f : output filename
PostgreSQL Client Applications
 pg_dumpall: similar to the pg_dump program,
except it dumps all of the databases to a file
pg_dumpall -U postgres -f D:\postgres_allDB.sql

 pg_restore: restore a PostgreSQL database from


an archive file created by pg_dump

9
PostgreSQL Client Applications
 pg_restore: restore a PostgreSQL database from
an archive file created by pg_dump

--restore from an archive file (format custom, tar, directory)


createdb -U postgres test2 (if test2 does not exist)
pg_restore -d test2 -U postgres D:\testDB_bk.dump

-- clean the database objects before recreating


pg_restore -c -d test2 -U postgres D:\testDB_bk.dump

10
PostgreSQL Client Applications
 pg_restore: can not restore from an plain format
➔ use psql

-- drop and create a new database if need


dropdb -U postgres test2
createdb -U postgres test2
-- restore
psql -d test2 -U postgres -f D:\testDB_bk.sql

11
12

You might also like