PostgreSQL - pencadangan & pemulihan¶
Perintah PostgreSQL¶
Perintah-perintah dasar PostgreSQL
createuser creates a new PostgreSQL role.
Usage:
createuser [OPTION]... [ROLENAME]
Options:
-c, --connection-limit=N connection limit for role (default: no limit)
-d, --createdb role can create new databases
-D, --no-createdb role cannot create databases (default)
-e, --echo show the commands being sent to the server
-g, --role=ROLE new role will be a member of this role
-i, --inherit role inherits privileges of roles it is a
member of (default)
-I, --no-inherit role does not inherit privileges
-l, --login role can login (default)
-L, --no-login role cannot login
-P, --pwprompt assign a password to new role
-r, --createrole role can create new roles
-R, --no-createrole role cannot create roles (default)
-s, --superuser role will be superuser
-S, --no-superuser role will not be superuser (default)
-V, --version output version information, then exit
--interactive prompt for missing role name and attributes rather
than using defaults
--replication role can initiate replication
--no-replication role cannot initiate replication
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as (not the one to create)
-w, --no-password never prompt for password
-W, --password force password prompt
Report bugs to <[email protected]>.
dropuser removes a PostgreSQL role.
Usage:
dropuser [OPTION]... [ROLENAME]
Options:
-e, --echo show the commands being sent to the server
-i, --interactive prompt before deleting anything, and prompt for
role name if not specified
-V, --version output version information, then exit
--if-exists don't report error if user doesn't exist
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as (not the one to drop)
-w, --no-password never prompt for password
-W, --password force password prompt
Report bugs to <[email protected]>.
createdb creates a PostgreSQL database.
Usage:
createdb [OPTION]... [DBNAME] [DESCRIPTION]
Options:
-D, --tablespace=TABLESPACE default tablespace for the database
-e, --echo show the commands being sent to the server
-E, --encoding=ENCODING encoding for the database
-l, --locale=LOCALE locale settings for the database
--lc-collate=LOCALE LC_COLLATE setting for the database
--lc-ctype=LOCALE LC_CTYPE setting for the database
-O, --owner=OWNER database user to own the new database
-T, --template=TEMPLATE template database to copy
-V, --version output version information, then exit
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
--maintenance-db=DBNAME alternate maintenance database
By default, a database with the same name as the current user is created.
Report bugs to <[email protected]>.
dropdb removes a PostgreSQL database.
Usage:
dropdb [OPTION]... DBNAME
Options:
-e, --echo show the commands being sent to the server
-i, --interactive prompt before deleting anything
-V, --version output version information, then exit
--if-exists don't report error if database doesn't exist
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
--maintenance-db=DBNAME alternate maintenance database
Report bugs to <[email protected]>.
pg_dump dumps a database as a text file or to other formats.
Usage:
pg_dump [OPTION]... [DBNAME]
General options:
-f, --file=FILENAME output file or directory name
-F, --format=c|d|t|p output file format (custom, directory, tar,
plain text (default))
-j, --jobs=NUM use this many parallel jobs to dump
-v, --verbose verbose mode
-V, --version output version information, then exit
-Z, --compress=0-9 compression level for compressed formats
--lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock
--no-sync do not wait for changes to be written safely to disk
-?, --help show this help, then exit
Options controlling the output content:
-a, --data-only dump only the data, not the schema
-b, --blobs include large objects in dump
-B, --no-blobs exclude large objects in dump
-c, --clean clean (drop) database objects before recreating
-C, --create include commands to create database in dump
-E, --encoding=ENCODING dump the data in encoding ENCODING
-n, --schema=PATTERN dump the specified schema(s) only
-N, --exclude-schema=PATTERN do NOT dump the specified schema(s)
-O, --no-owner skip restoration of object ownership in
plain-text format
-s, --schema-only dump only the schema, no data
-S, --superuser=NAME superuser user name to use in plain-text format
-t, --table=PATTERN dump the specified table(s) only
-T, --exclude-table=PATTERN do NOT dump the specified table(s)
-x, --no-privileges do not dump privileges (grant/revoke)
--binary-upgrade for use by upgrade utilities only
--column-inserts dump data as INSERT commands with column names
--disable-dollar-quoting disable dollar quoting, use SQL standard quoting
--disable-triggers disable triggers during data-only restore
--enable-row-security enable row security (dump only content user has
access to)
--exclude-table-data=PATTERN do NOT dump data for the specified table(s)
--extra-float-digits=NUM override default setting for extra_float_digits
--if-exists use IF EXISTS when dropping objects
--inserts dump data as INSERT commands, rather than COPY
--load-via-partition-root load partitions via the root table
--no-comments do not dump comments
--no-publications do not dump publications
--no-security-labels do not dump security label assignments
--no-subscriptions do not dump subscriptions
--no-synchronized-snapshots do not use synchronized snapshots in parallel jobs
--no-tablespaces do not dump tablespace assignments
--no-unlogged-table-data do not dump unlogged table data
--on-conflict-do-nothing add ON CONFLICT DO NOTHING to INSERT commands
--quote-all-identifiers quote all identifiers, even if not key words
--rows-per-insert=NROWS number of rows per INSERT; implies --inserts
--section=SECTION dump named section (pre-data, data, or post-data)
--serializable-deferrable wait until the dump can run without anomalies
--snapshot=SNAPSHOT use given snapshot for the dump
--strict-names require table and/or schema include patterns to
match at least one entity each
--use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead of
ALTER OWNER commands to set ownership
Connection options:
-d, --dbname=DBNAME database to dump
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=NAME connect as specified database user
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
--role=ROLENAME do SET ROLE before dump
If no database name is supplied, then the PGDATABASE environment
variable value is used.
Report bugs to <[email protected]>.
pg_restore restores a PostgreSQL database from an archive created by pg_dump.
Usage:
pg_restore [OPTION]... [FILE]
General options:
-d, --dbname=NAME connect to database name
-f, --file=FILENAME output file name (- for stdout)
-F, --format=c|d|t backup file format (should be automatic)
-l, --list print summarized TOC of the archive
-v, --verbose verbose mode
-V, --version output version information, then exit
-?, --help show this help, then exit
Options controlling the restore:
-a, --data-only restore only the data, no schema
-c, --clean clean (drop) database objects before recreating
-C, --create create the target database
-e, --exit-on-error exit on error, default is to continue
-I, --index=NAME restore named index
-j, --jobs=NUM use this many parallel jobs to restore
-L, --use-list=FILENAME use table of contents from this file for
selecting/ordering output
-n, --schema=NAME restore only objects in this schema
-N, --exclude-schema=NAME do not restore objects in this schema
-O, --no-owner skip restoration of object ownership
-P, --function=NAME(args) restore named function
-s, --schema-only restore only the schema, no data
-S, --superuser=NAME superuser user name to use for disabling triggers
-t, --table=NAME restore named relation (table, view, etc.)
-T, --trigger=NAME restore named trigger
-x, --no-privileges skip restoration of access privileges (grant/revoke)
-1, --single-transaction restore as a single transaction
--disable-triggers disable triggers during data-only restore
--enable-row-security enable row security
--if-exists use IF EXISTS when dropping objects
--no-comments do not restore comments
--no-data-for-failed-tables do not restore data of tables that could not be
created
--no-publications do not restore publications
--no-security-labels do not restore security labels
--no-subscriptions do not restore subscriptions
--no-tablespaces do not restore tablespace assignments
--section=SECTION restore named section (pre-data, data, or post-data)
--strict-names require table and/or schema include patterns to
match at least one entity each
--use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead of
ALTER OWNER commands to set ownership
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=NAME connect as specified database user
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
--role=ROLENAME do SET ROLE before restore
The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified
multiple times to select multiple objects.
If no input file name is supplied, then standard input is used.
Report bugs to <[email protected]>.
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: "deploy")
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
--help=commands list backslash commands, then exit
--help=variables list special variables, then exit
Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
--csv CSV (Comma-Separated Values) table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "deploy")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.
Report bugs to <[email protected]>.
Pencadangan & pemindahan¶
Proses ini dilakukan pada peladen tujuan pangkalan data
Variabel
# Peladen sumber migrasi
HOS_PD_SUMBER="10.20.30.100"
PENGGUNA_PD_SUMBER="pengguna_pd_sumber"
NAMA_PD_SUMBER="nama_pd_sumber"
BERKAS_CADANGAN_PD="$HOME/`date +%Y%m%d%H%M`.$NAMA_PD_SUMBER.dump.sql"
Mencadangkan pangkalan data
pg_dump --host=$HOS_PD_SUMBER \
--username=$PENGGUNA_PD_SUMBER \
--dbname=$NAMA_PD_SUMBER \
--verbose --no-owner --no-privileges --clean --create --format=c \
> $BERKAS_CADANGAN_PD
pg_dump -h $HOS_PD_SUMBER \
-U $PENGGUNA_PD_SUMBER \
-d $NAMA_PD_SUMBER \
-v -O -x -c -C -Fc \
> $BERKAS_CADANGAN_PD
Pemulihan¶
Proses ini dilakukan pada peladen tujuan pangkalan data
Variabel
# Peladen tujuan migrasi
HOS_PD_TUJUAN="10.20.30.200"
PENGGUNA_PD_ADMIN="pengguna_pd_admin"
PENGGUNA_PD_APL="pengguna_pd_apl"
NAMA_PD_APL="nama_pd_apl"
Membuat pengguna
createuser --host=$HOS_PD_TUJUAN \
--username=$PENGGUNA_PD_ADMIN \
--echo \
--createdb \
--pwprompt $PENGGUNA_PD_APL
Memulihkan pangkalan data
Proses pemulihan pangkalan data dilakukn dengan menggunakan pengguna yang sebelumnya sudah dibuat dan memiliki hak akses CREATEDB. Hal ini dilakukan agar kepemilikan pangkalan data, langsung melekat kepada pengguna yang melakukan pemulihan.
# Format parameter lengkap
pg_restore --host=$HOS_PD_TUJUAN \
--username=$PENGGUNA_PD_APL \
--dbname=$NAMA_PD_APL \
--verbose --exit-on-error --no-owner --no-privileges --clean --if-exists \
$BERKAS_CADANGAN_PD
pg_restore -h $HOS_PD_TUJUAN \
-U $PENGGUNA_PD_APL \
-d $NAMA_PD_APL \
-v -e -O -x -c --if-exists \
$BERKAS_CADANGAN_PD
Pembersihan¶
Menghapus pengguna dan pangkalan data
dropdb --host=$HOS_PD_TUJUAN \
--username=$PENGGUNA_PD_APL \
$NAMA_PD
dropuser --host=$HOS_PD_TUJUAN \
--username=$PENGGUNA_PD_ADMIN \
--echo \
$PENGGUNA_PD_APL
Pemeliharaan¶
Mengubah nama pangkalan data
-- kueri_ubah_pd.sql
ALTER DATABASE nama_pd_lama RENAME TO nama_pd_baru;
KUERI_UBAH_PD="ALTER DATABASE nama_pd_lama RENAME TO nama_pd_baru;"
psql --host=$HOS_PELADEN_PD \
--username=$PENGGUNA_PD_ADM \
--dbname=$NAMA_PD_APL \
--echo-queries \
--command="${KUERI_UBAH_PD}"
psql -h $HOS_PELADEN_PD \
-U $PENGGUNA_PD_ADM \
-d $NAMA_PD_APL \
-e \
-c "${KUERI_UBAH_PD}"