System catalogs are defined in a schema named pg_catalog.

The system catalog is updated whenever a record is updated.

The system catalog is updated through the ANALYZE command.

The system catalog is referenced by the PostgreSQL planner and cannot be accessed by the user.

VACUUM and ANALYZE can be run concurrently.

autovacuum performs VACUUM and ANALYZE operations on all tables.

By default, autovacuum is disabled.

Running VACUUM on psql and using the vacuumdb command have the same effect.

pg_config can be used to check the PostgreSQL GUC parameters.

pg_controldata can be used to check control information about WAL and timelines in a PostgreSQL database cluster.

pg_isready can be used to check the startup status of the PostgreSQL server.

pg_resetwal can be used to clean up WAL files.

The format of the information schema is as specified in the SQL standard.

The amount of information that can be seen in the information schema and the system catalog is equivalent.

The PostgreSQL system catalog is defined by a schema named pg_system.

The PostgreSQL system catalogs typically start with the prefix 'pg_'.

The pg_database catalog does not exist for each database, but rather one for each database cluster.

WAL files and WAL archiving are used for Point In Time Recovery (PITR).

The size of each WAL file is 8KB.

The file structures of WAL files and WAL archives are different.

The output destination for the WAL archive is set using the "restore_command" parameter.

By default, WAL files are stored in the "$PGDATA/pg_wal" directory.

The shutdown method can be selected with the -m option, but the default is "fast" since PostgreSQL12.

The server does not stop as long as there is a connection with the client in any of the modes.

The "immediate" mode performs crash recovery on the next server start.

The "immediate" mode prevents changes to the PostgreSQL configuration file from taking effect the next time the server starts.

The -t option allows you to specify the number of seconds before the stop operation starts.

Connect to a running PostgreSQL server and use the pg_start_backup() and pg_stop_backup() functions.

After running the pg_stop_backup() function, two files are automatically generated: backup_label and tablespace_map.

If no options are specified for the pg_start_backup() or pg_stop_backup() functions, they operate in exclusive mode.

Exclusive mode is not recommended.

Backups taken with non-exclusive low-level backups are not available for PITR.

The same can be achieved by connecting to a database using psql etc., and executing the DROP DATABASE command.

You can delete either a database created with the createdb command or a database created with the CREATE DATABASE command.

You can also delete databases that are running on other hosts.

You must be a superuser or have CREATEDB privileges to execute it.

The database to be deleted must be empty, otherwise an error occurs.

You must have superuser privileges on the database to execute the command.

Users can also be created in databases running on other hosts.

If you optionally specify, for example, "-P passwd," the new user's password will be 'passwd'.

You can connect to a database in psql and use the CREATE USER command to accomplish the same thing as the createuser command.

You can use the dropuser command to delete a user created with the createuser command, or you can connect to a database, using psql etc., and run the DROP USER command.

It should be run as the user who will be the database administrator.

On Linux, since root is the system administrator, initdb should also be run as root.

A database cluster is created and it becomes possible to connect to the database using, for example, psql.

The directory in which the database cluster will be created can be created in advance, but it must be empty.

Three databases named template0, template1, and postgres are created.

pg_config can change the values of configuration parameters in a PostgreSQL database.

pg_controldata can be used to limit the size of data sent and received over the network.

pg_ctl can start or stop a PostgreSQL database.

pg_isready can check whether a PostgreSQL database is ready for connection.

pg_resetwal can be used as a last resort when the database server cannot be started due to corruption of database files.

By default, only the user who executes the command can access the files in the database cluster.

The command is executed by the user who will become the owner of the database server process.

The command should be run as the root user for enhanced security.

The created database cluster contains two template databases and one regular database.

Because the character set encoding specified during database cluster creation applies to all databases within the cluster, it is crucial to be careful about what is specified.

If you have access privileges, you can GRANT them to other users.

Only superusers and table owners can revoke GRANTED access privileges with REVOKE.

The owner of a table has all privileges on the table, so if the SELECT privilege is revoked with REVOKE, you can still SELECT data from the table.

You GRANT a SELECT privilege on table t1 to users u1 and public, then REVOKE this privilege from u1. In this case, the user u1 cannot SELECT data of the table t1.

After user s1 GRANTS user u2 a SELECT privilege on table t2, user s2 also GRANTS it to the same user u2. After this, user s1 REVOKES the SELECT privilege on t2 from u2. At this time, the user u2 can select data of the table t2. It is assumed that both users s1 and s2 have the privilege to GRANT the SELECT privilege on t2.

VACUUM operation is performed on all tables at regular intervals.

VACUUM is performed on a table where the amount of data updates exceeds a certain threshold.

It is designed to be executed only during periods when the database load is low.

It is designed to prevent many concurrent VACUUM executions.

Temporary tables are not subject to autovacuum, so you must issue SQL to run VACUUM if necessary.

Users can run ANALYZE on the tables they own.

Running ANALYZE is important to optimize performance (speed) when executing SQL such as SELECT.

ANALYZE is very intensive because it examines all the data in the target table. It is preferable to execute it at night, when there is little database activity.

The VACUUM command also automatically runs ANALYZE on the same table.

When you run autovacuum, ANALYZE runs automatically as well as VACUUM.

Create a new database user.

Change the password of a database user.

To grant user creation permission to a database user.

Change the database user to an administrator user.

Grant database users access to specific tables.

Grant a user access to objects, such as SELECT from a table.

Grant a role access to objects, such as SELECT from a table.

Grant a role the permission to create a database (CREATEDB).

Grant all privileges assigned to a role to a user in a batch.

Grant all privileges assigned to a role to another role in a batch.

Start the database by executing the postgres command with the appropriate options.

Start the database by running the pg_ctl command with the appropriate options.

Shut down the database by executing the postgres command with the appropriate options.

Shut down the database by running the pg_ctl command with the appropriate options.

Shut down the database by sending an appropriate signal to the postgres process, such as with the kill command.

The contents of the file /path/to/filename are added to the table tablex.

Not only is it a convenient command for loading data from a file, it is also faster than using SQL INSERT.

Since /path/to/filename is a file on the database server, the user running the database process (typically postgres) must have access privilege.

/path/to/filename is a tab-delimited text file.

You must have database administrator privileges to execute this command.

To use point-in-time recovery (PITR), WAL archiving must be enabled.

This function combines multiple WAL files into a single file.

The command specified in the archive_command configuration parameter is executed.

Attention must be paid as the command is not re-executed even if it fails.

PostgreSQL keeps track of whether a command has been executed, but does not know what the command actually does or whether it is doing the right operations for WAL archiving.

Enable WAL archiving.

Connect to the database and run pg_start_backup.

Stop the database server.

Use the tar command to obtain a copy of the entire database cluster.

Connect to the database and run pg_stop_backup.

The PostgreSQL version must be the same as host X, 12.4.

The major version of PostgreSQL must be 12 and the minor version 4 or higher.

If the major version of PostgreSQL is 12, the minor version can be either greater than, equal to, or less than 4.

The major version of PostgreSQL must be at least 12.

Any version of PostgreSQL can be used as long as it can run on Linux.

You cannot back up users or roles in a database.

By specifying the -a option, you can output a backup of just the data without including table definitions.

By specifying the -s option, you can output a backup that does not contain data, but only a table definition.

The -F option specifies the output format: p outputs a text backup, c, d or t outputs a binary backup.

Of the binary formats, c outputs a physical backup, while the other formats output a logical backup.

when connecting from machine cl1 to database db1 by user u1

When connecting from machine cl1 to database db2 by user u1

When connecting from machine cl1 to database db1 by user u2

When connecting from machine cl2 to database db1 by user u2

When connecting from machine cl2 to database db2 by user u2

When connecting from machine cl2 to database db2 by user u1

listen_addresses = ''

listen_addresses = '*'

listen_addresses = 'localhost'

listen_addresses = '192.168.1.11'

listen_addresses = '192.168.1.51'

For Boolean parameter values, you use on/off, but you can also use t/f, y/n, or 1/0 instead of on/off.

The default units for time parameter values are seconds, but you can explicitly specify units such as 100 ms or 1 min.

The default unit for the value of the parameter that specifies the amount of memory is kilobytes, but you can also specify the unit explicitly, for example, 100 MB or 1 GB.

Values for parameters that specify numeric values are specified in decimal numbers, with the values beginning with 0x interpreted as hexadecimal and values beginning with 0 interpreted as octal numbers.

Values for parameters that specify strings must be enclosed in single quotes, but if the value contains single quotes, two quotation marks must be placed in sequence, for example 'don''t'.

When starting psql, specify the --mode=csv option, and execute the following from the psql prompt:
SELECT ... > output.csv

When starting psql, specify >output.csv option to redirect the output, and then execute the following commands in the specified order:
\format csv
SELECT ...

After starting psql, execute the following commands in the specified order:
\o output.csv
\pset format csv
SELECT ....

Execute the following from the command line:
psql -c "SELECT ..." --csv > output.csv

Execute the following from the command line:
psql -c "SELECT ..." --format=csv > output.csv

The createuser can only be used against a database running on a local host, and not against a database running on a remote host.

At runtime, you are always prompted for the administrator user password.

Because the postgres user is used when connecting to the database, the postgres user must be granted appropriate privileges.

For security reasons, it is not possible to create a superuser with createuser, so you need to connect to the database, for example in psql, and execute create user (or create role).

By default, a user with database connection privileges is created, but you can also create a role without connection privileges (ROLE) by specifying the option.

Immediately after installation, two template databases and one regular database are included.

High availability can be achieved by building and mirroring multiple databases in a database cluster.

Since the character set of each database can be set differently for each database, one database in the database cluster can be set to UTF8, and another database can be set to EUC_JP, and so on.

Because information about database users is kept in each database that is built in a database cluster, users must be created by performing an operation such as CREATE USER on each database to allow access to a particular database.

The pg_dump command for database backups cannot back up common data in a database cluster, so you must use the pg_dumpall command to back up that portion.

Name of the database cluster

Directory where the database cluster is stored

Port number for connecting over TCP/IP

Encoding system for template database

Locale of database cluster

SELECT

INSERT

UPDATE

DELETE

REFERENCES

It is a collection of views stored in the schema named information_schema.

It stores various information about the database, which can be retrieved using a SELECT statement.

Table information can be checked by referencing 'tables', and column information can be checked by referencing 'columns'.

As it is specified in the SQL standard, all RDBMSs, including PostgreSQL, support information schemas.

As it adheres to the SQL standard, information about PostgreSQL's proprietary extensions cannot be obtained from the information schema.

VACUUM only reclaims unneeded space unless specified by an option, whereas autovacuum both reclaims unneeded space and analyzes (ANALYZE) tables.

While a list of target tables must be specified for VACUUM, autovacuum automatically targets tables with more than a certain amount of inserted, updated, or deleted data.

VACUUM can target temporary tables, but autovacuum does not.

VACUUM can reduce the file size by specifying options, but autovacuum has no equivalent feature.

VACUUM can be made to run automatically on a regular basis, for example, using an OS feature. Autovacuum runs automatically when the PostgreSQL server is running and the transaction volume is low, so there is no need to configure OS features.

VACUUM FULL targets all tables in the database, but VACUUM only targets specific tables that you specify.

VACUUM FULL also runs ANALYZE on the table, but VACUUM does not run ANALYZE.

VACUUM FULL acquires an exclusive lock on the table being processed, which conflicts with normal table read/write operations. VACUUM does not acquire an exclusive lock.

Running VACUUM FULL typically reduces the size of the files that make up the table, while running VACUUM typically does not change the size of these files.

VACUUM can be executed by issuing the vacuumdb command from the OS command line, but VACUUM FULL cannot be executed from the command line.

Connect to a database in psql and execute:
create role cdbuser login createdb

Connect to a database in psql and execute:
create user cdbuser db

Connect to a database in psql and execute:
create user name=cdbuser grant=dbcreate

From the OS command line, execute:
createuser -d cdbuser

From the OS command line, execute:
createuser -D cdbuser

CREATE ROLE can be executed by normal users, but CREATE USER requires superuser privileges.

Roles created with CREATE ROLE can be granted to users with the GRANT command, but users created with CREATE USER cannot be granted to other users with GRANT.

The LOGIN attribute defaults to NOLOGIN for a role created with CREATE ROLE, and to LOGIN for a user created with CREATE USER.

When a user is created with CREATE USER, a schema with the same name is automatically created, but with CREATE ROLE, no schema is created.

A command called 'createuser' is provided to perform the operation equivalent to CREATE USER from the OS command line, but no command named 'createrole' is provided.

It waits for a connected client session, if any, to terminate.

For running transactions, commit is performed automatically.

For running transactions, rollback is performed automatically.

Recovery occurs on reboot.

Since PostgreSQL version 9.5, this mode is the default, so the behavior is the same without “-m fast.”

When a table is backed up in text format by the pg_dump command, the data portion of the table is output as the COPY statement.

The meta-command \copy executed by psql internally calls SQL COPY.

To execute COPY FROM on a table, you must have INSERT privilege on the table.

To execute COPY TO on a table, you must have SELECT privilege on the table.

If no option is specified, I/O is performed using a CSV file.

WAL records all changes made to the database, and immediately after a data file is updated, the changes are written to WAL.

If the database server is not terminated normally, such as by shutting it down with the immediate option, the data file is recovered by referencing the WAL file at restart.

WAL files were created under the pg_xlog directory of a database cluster until PostgreSQL version 9.6, but after PostgreSQL version 10 they are created under the pg_wal directory of a database cluster.

Point-in-time recovery (PITR) requires that the generated WAL files be copied to a directory outside the database cluster and saved by setting the archive_command parameter appropriately.

In PITR, the wal_level parameter must be set appropriately so that detailed information is written to the WAL file.

Connect to the database as an administrator and run pg_start_backup.

Stop the database.

Copy the entire directory of the database cluster using an OS command such as tar.

Connect to the database as an administrator and run pg_stop_backup.

Instead of a series of steps above, run the pg_basebackup command from the OS command line.

Use a command such as cp to create a copy of the database cluster directory.

Use a command such as tar to create an archive of the database cluster directory.

Create a duplicate of the database cluster's directory on another host on the network using a command such as rsync.

Use the pg_basebackup command to create a base backup of the database cluster.

Use the pg_dumpall command to create a backup of the entire database cluster.

The pg_dump command can create a database backup in text format.

The pg_dumpall command can create a binary backup of an entire database cluster.

The pg_basebackup command can create a base backup for point-in-time recovery (PITR).

The psql command can restore from a text backup.

The pg_restore command can restore from a binary backup.

It was used until PostgreSQL version 9.6, but was deprecated in version 10.

You can manage passwords for database users.

You can manage various user privileges granted or revoked by GRANT/REVOKE.

You can control whether other machines on the network can connect to the database.

You can manage the authentication method used for connection to each database in a database cluster.

Boolean parameters can have values such as on, true, t, yes, or y for representing 'true'.

Numeric parameter values are written with commas in every 3 digits for readability, such as 12,345.

Units of time include ms (milliseconds), s (seconds), m (minutes), h (hours), and d (days), and they are case-insensitive.

Units of memory include B, kB, MB, GB, and TB. Only k in kilobytes is written in lowercase and all others should be in uppercase.

When writing time or memory parameter values, if you omit the unit and write only a numeric value, milliseconds and bytes are used as the defaults, respectively.

-c

-e

-f

-s

-x

By default, three databases are created: template0, template1, and postgres.

The database cluster must be running.

A database can be created on a database cluster running on other hosts on the network.

Anyone with user privileges on the database cluster can execute it.

It is also available for non-PostgreSQL databases such as MySQL.