Postgresql Commands Cheat Sheet

PostgreSQL comes with a ton of configuration settings, but with documentationscattered all over The Manual!Here is a handy cheat sheet of all Postgres 10 configuration options, splitinto categories, with clickable links that go to the official documentation.Enjoy!

Postgresql Commands Cheat Sheet Cheat
Postgresql Commands Cheat Sheet Download
Postgresql Commands Cheat Sheet Free

Postgresql Basic Commands Login to postgresql. Psql -U postgres psql -d mydb -U myuser -W psql -h myhost -d mydb -U myuser -W psql -U myuser -h myhost 'dbname=mydb. PostgreSQL Shell Cheat Sheet Starting the server. After you have run this command the first time, postgres will start automatically whenever you boot up your computer.

Autovacuum

autovacuum	Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor	Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold	Minimum number of tuple inserts, updates, or deletes prior to analyze.
autovacuum_freeze_max_age	Age at which to autovacuum a table to prevent transaction ID wraparound.
autovacuum_max_workers	Sets the maximum number of simultaneously running autovacuum worker processes.
autovacuum_multixact_freeze_max_age	Multixact age at which to autovacuum a table to prevent multixact wraparound.
autovacuum_naptime	Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay	Vacuum cost delay in milliseconds, for autovacuum.
autovacuum_vacuum_cost_limit	Vacuum cost amount available before napping, for autovacuum.
autovacuum_vacuum_scale_factor	Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold	Minimum number of tuple updates or deletes prior to vacuum.

Client Connection Defaults / Locale and Formatting

client_encoding	Sets the client’s character set encoding.
DateStyle	Sets the display format for date and time values.
default_text_search_config	Sets default text search configuration.
extra_float_digits	Sets the number of digits displayed for floating-point values.
IntervalStyle	Sets the display format for interval values.
lc_collate	Shows the collation order locale.
lc_ctype	Shows the character classification and case conversion locale.
lc_messages	Sets the language in which messages are displayed.
lc_monetary	Sets the locale for formatting monetary amounts.
lc_numeric	Sets the locale for formatting numbers.
lc_time	Sets the locale for formatting date and time values.
server_encoding	Sets the server (database) character set encoding.
TimeZone	Sets the time zone for displaying and interpreting time stamps.
timezone_abbreviations	Selects a file of time zone abbreviations.

Client Connection Defaults / Other Defaults

dynamic_library_path	Sets the path for dynamically loadable modules.
gin_fuzzy_search_limit	Sets the maximum allowed result for exact search by GIN.
tcp_keepalives_count	Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle	Time between issuing TCP keepalives.
tcp_keepalives_interval	Time between TCP keepalive retransmits.

Client Connection Defaults / Shared Library Preloading

local_preload_libraries	Lists unprivileged shared libraries to preload into each backend.
session_preload_libraries	Lists shared libraries to preload into each backend.
shared_preload_libraries	Lists shared libraries to preload into server.

Client Connection Defaults / Statement Behavior

bytea_output	Sets the output format for bytea.
check_function_bodies	Check function bodies during CREATE FUNCTION.
default_tablespace	Sets the default tablespace to create tables and indexes in.
default_transaction_deferrable	Sets the default deferrable status of new transactions.
default_transaction_isolation	Sets the transaction isolation level of each new transaction.
default_transaction_read_only	Sets the default read-only status of new transactions.
gin_pending_list_limit	Sets the maximum size of the pending list for GIN index.
idle_in_transaction_session_timeout	Sets the maximum allowed duration of any idling transaction.
lock_timeout	Sets the maximum allowed duration of any wait for a lock.
search_path	Sets the schema search order for names that are not schema-qualified.
session_replication_role	Sets the session’s behavior for triggers and rewrite rules.
statement_timeout	Sets the maximum allowed duration of any statement.
temp_tablespaces	Sets the tablespace(s) to use for temporary tables and sort files.
transaction_deferrable	Whether to defer a read-only serializable transaction until it can be executed with no possible serialization failures.
transaction_isolation	Sets the current transaction’s isolation level.
transaction_read_only	Sets the current transaction’s read-only status.
vacuum_freeze_min_age	Minimum age at which VACUUM should freeze a table row.
vacuum_freeze_table_age	Age at which VACUUM should scan whole table to freeze tuples.
vacuum_multixact_freeze_min_age	Minimum age at which VACUUM should freeze a MultiXactId in a table row.
vacuum_multixact_freeze_table_age	Multixact age at which VACUUM should scan whole table to freeze tuples.
xmlbinary	Sets how binary values are to be encoded in XML.
xmloption	Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.

Connections and Authentication / Connection Settings

bonjour	Enables advertising the server via Bonjour.
bonjour_name	Sets the Bonjour service name.
listen_addresses	Sets the host name or IP address(es) to listen to.
max_connections	Sets the maximum number of concurrent connections.
port	Sets the TCP port the server listens on.
superuser_reserved_connections	Sets the number of connection slots reserved for superusers.
unix_socket_directories	Sets the directories where Unix-domain sockets will be created.
unix_socket_group	Sets the owning group of the Unix-domain socket.
unix_socket_permissions	Sets the access permissions of the Unix-domain socket.

Connections and Authentication / Security and Authentication

authentication_timeout	Sets the maximum allowed time to complete client authentication.
db_user_namespace	Enables per-database user names.
krb_caseins_users	Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive.
krb_server_keyfile	Sets the location of the Kerberos server key file.
password_encryption	Encrypt passwords.
row_security	Enable row security.
ssl	Enables SSL connections.
ssl_ca_file	Location of the SSL certificate authority file.
ssl_cert_file	Location of the SSL server certificate file.
ssl_ciphers	Sets the list of allowed SSL ciphers.
ssl_crl_file	Location of the SSL certificate revocation list file.
ssl_dh_params_file	Location of the SSL DH parameters file.
ssl_ecdh_curve	Sets the curve to use for ECDH.
ssl_key_file	Location of the SSL server private key file.
ssl_prefer_server_ciphers	Give priority to server ciphersuite order.

Developer Options

allow_system_table_mods	Allows modifications of the structure of system tables.
ignore_checksum_failure	Continues processing after a checksum failure.
ignore_system_indexes	Disables reading from system indexes.
post_auth_delay	Waits N seconds on connection startup after authentication.
pre_auth_delay	Waits N seconds on connection startup before authentication.
trace_notify	Generates debugging output for LISTEN and NOTIFY.
trace_recovery_messages	Enables logging of recovery-related debugging information.
trace_sort	Emit information about resource usage in sorting.
wal_consistency_checking	Sets the WAL resource managers for which WAL consistency checks are done.
zero_damaged_pages	Continues processing past damaged page headers.

Error Handling

exit_on_error	Terminate session on any error.
restart_after_crash	Reinitialize server after backend crash.

File Locations

config_file	Sets the server’s main configuration file.
data_directory	Sets the server’s data directory.
external_pid_file	Writes the postmaster PID to the specified file.
hba_file	Sets the server’s “hba” configuration file.
ident_file	Sets the server’s “ident” configuration file.

Lock Management

deadlock_timeout	Sets the time to wait on a lock before checking for deadlock.
max_locks_per_transaction	Sets the maximum number of locks per transaction.
max_pred_locks_per_page	Sets the maximum number of predicate-locked tuples per page.
max_pred_locks_per_relation	Sets the maximum number of predicate-locked pages and tuples per relation.
max_pred_locks_per_transaction	Sets the maximum number of predicate locks per transaction.

Preset Options

block_size	Shows the size of a disk block.
data_checksums	Shows whether data checksums are turned on for this cluster.
debug_assertions	Shows whether the running server has assertion checks enabled.
integer_datetimes	Datetimes are integer based.
max_function_args	Shows the maximum number of function arguments.
max_identifier_length	Shows the maximum identifier length.
max_index_keys	Shows the maximum number of index keys.
segment_size	Shows the number of pages per disk file.
server_version	Shows the server version.
server_version_num	Shows the server version as an integer.
wal_block_size	Shows the block size in the write ahead log.
wal_segment_size	Shows the number of pages per write ahead log segment.

Process Title

cluster_name	Sets the name of the cluster, which is included in the process title.
update_process_title	Updates the process title to show the active SQL command.

Query Tuning / Genetic Query Optimizer

geqo	Enables genetic query optimization.
geqo_effort	GEQO: effort is used to set the default for other GEQO parameters.
geqo_generations	GEQO: number of iterations of the algorithm.
geqo_pool_size	GEQO: number of individuals in the population.
geqo_seed	GEQO: seed for random path selection.
geqo_selection_bias	GEQO: selective pressure within the population.
geqo_threshold	Sets the threshold of FROM items beyond which GEQO is used.

Query Tuning / Other Planner Options

constraint_exclusion	Enables the planner to use constraints to optimize queries.
cursor_tuple_fraction	Sets the planner’s estimate of the fraction of a cursor’s rows that will be retrieved.
default_statistics_target	Sets the default statistics target.
force_parallel_mode	Forces use of parallel query facilities.
from_collapse_limit	Sets the FROM-list size beyond which subqueries are not collapsed.
join_collapse_limit	Sets the FROM-list size beyond which JOIN constructs are not flattened.

Query Tuning / Planner Cost Constants

cpu_index_tuple_cost	Sets the planner’s estimate of the cost of processing each index entry during an index scan.
cpu_operator_cost	Sets the planner’s estimate of the cost of processing each operator or function call.
cpu_tuple_cost	Sets the planner’s estimate of the cost of processing each tuple (row).
effective_cache_size	Sets the planner’s assumption about the size of the disk cache.
min_parallel_index_scan_size	Sets the minimum amount of index data for a parallel scan.
min_parallel_table_scan_size	Sets the minimum amount of table data for a parallel scan.
parallel_setup_cost	Sets the planner’s estimate of the cost of starting up worker processes for parallel query.
parallel_tuple_cost	Sets the planner’s estimate of the cost of passing each tuple (row) from worker to master backend.
random_page_cost	Sets the planner’s estimate of the cost of a nonsequentially fetched disk page.
seq_page_cost	Sets the planner’s estimate of the cost of a sequentially fetched disk page.

Query Tuning / Planner Method Configuration

enable_bitmapscan	Enables the planner’s use of bitmap-scan plans.
enable_gathermerge	Enables the planner’s use of gather merge plans.
enable_hashagg	Enables the planner’s use of hashed aggregation plans.
enable_hashjoin	Enables the planner’s use of hash join plans.
enable_indexonlyscan	Enables the planner’s use of index-only-scan plans.
enable_indexscan	Enables the planner’s use of index-scan plans.
enable_material	Enables the planner’s use of materialization.
enable_mergejoin	Enables the planner’s use of merge join plans.
enable_nestloop	Enables the planner’s use of nested-loop join plans.
enable_seqscan	Enables the planner’s use of sequential-scan plans.
enable_sort	Enables the planner’s use of explicit sort steps.
enable_tidscan	Enables the planner’s use of TID scan plans.

Replication

track_commit_timestamp

Collects transaction commit time.

Replication / Master Server

synchronous_standby_names	Number of synchronous standbys and list of names of potential synchronous ones.
vacuum_defer_cleanup_age	Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.

Replication / Sending Servers

max_replication_slots	Sets the maximum number of simultaneously defined replication slots.
max_wal_senders	Sets the maximum number of simultaneously running WAL sender processes.
wal_keep_segments	Sets the number of WAL files held for standby servers.
wal_sender_timeout	Sets the maximum time to wait for WAL replication.

Replication / Standby Servers

hot_standby	Allows connections and queries during recovery.
hot_standby_feedback	Allows feedback from a hot standby to the primary that will avoid query conflicts.
max_standby_archive_delay	Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data.
max_standby_streaming_delay	Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data.
wal_receiver_status_interval	Sets the maximum interval between WAL receiver status reports to the primary.
wal_receiver_timeout	Sets the maximum wait time to receive data from the primary.
wal_retrieve_retry_interval	Sets the time to wait before retrying to retrieve WAL after a failed attempt.

Replication / Subscribers

max_logical_replication_workers	Maximum number of logical replication worker processes.
max_sync_workers_per_subscription	Maximum number of table synchronization workers per subscription.

Reporting and Logging / What to Log

application_name	Sets the application name to be reported in statistics and logs.
debug_pretty_print	Indents parse and plan tree displays.
debug_print_parse	Logs each query’s parse tree.
debug_print_plan	Logs each query’s execution plan.
debug_print_rewritten	Logs each query’s rewritten parse tree.
log_autovacuum_min_duration	Sets the minimum execution time above which autovacuum actions will be logged.
log_checkpoints	Logs each checkpoint.
log_connections	Logs each successful connection.
log_disconnections	Logs end of a session, including duration.
log_duration	Logs the duration of each completed SQL statement.
log_error_verbosity	Sets the verbosity of logged messages.
log_hostname	Logs the host name in the connection logs.
log_line_prefix	Controls information prefixed to each log line.
log_lock_waits	Logs long lock waits.
log_replication_commands	Logs each replication command.
log_statement	Sets the type of statements logged.
log_temp_files	Log the use of temporary files larger than this number of kilobytes.
log_timezone	Sets the time zone to use in log messages.

Reporting and Logging / When to Log

client_min_messages	Sets the message levels that are sent to the client.
log_min_duration_statement	Sets the minimum execution time above which statements will be logged.
log_min_error_statement	Causes all statements generating error at or above this level to be logged.
log_min_messages	Sets the message levels that are logged.

Reporting and Logging / Where to Log

event_source	Sets the application name used to identify PostgreSQL messages in the event log.
log_destination	Sets the destination for server log output.
log_directory	Sets the destination directory for log files.
log_file_mode	Sets the file permissions for log files.
log_filename	Sets the file name pattern for log files.
log_rotation_age	Automatic log file rotation will occur after N minutes.
log_rotation_size	Automatic log file rotation will occur after N kilobytes.
log_truncate_on_rotation	Truncate existing log files of same name during log rotation.
logging_collector	Start a subprocess to capture stderr output and/or csvlogs into log files.
syslog_facility	Sets the syslog “facility” to be used when syslog enabled.
syslog_ident	Sets the program name used to identify PostgreSQL messages in syslog.
syslog_sequence_numbers	Add sequence number to syslog messages to avoid duplicate suppression.
syslog_split_messages	Split messages sent to syslog by lines and to fit into 1024 bytes.

Resource Usage / Asynchronous Behavior

backend_flush_after	Number of pages after which previously performed writes are flushed to disk.
effective_io_concurrency	Number of simultaneous requests that can be handled efficiently by the disk subsystem.
max_parallel_workers	Sets the maximum number of parallel workers than can be active at one time.
max_parallel_workers_per_gather	Sets the maximum number of parallel processes per executor node.
max_worker_processes	Maximum number of concurrent worker processes.
old_snapshot_threshold	Time before a snapshot is too old to read pages changed after the snapshot was taken.

Resource Usage / Background Writer

bgwriter_delay	Background writer sleep time between rounds.
bgwriter_flush_after	Number of pages after which previously performed writes are flushed to disk.
bgwriter_lru_maxpages	Background writer maximum number of LRU pages to flush per round.
bgwriter_lru_multiplier	Multiple of the average buffer usage to free per round.

Resource Usage / Cost-Based Vacuum Delay

vacuum_cost_delay	Vacuum cost delay in milliseconds.
vacuum_cost_limit	Vacuum cost amount available before napping.
vacuum_cost_page_dirty	Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit	Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss	Vacuum cost for a page not found in the buffer cache.

Resource Usage / Disk

temp_file_limit

Limits the total size of all temporary files used by each process.

Resource Usage / Kernel Resources

max_files_per_process

Sets the maximum number of simultaneously open files for each server process.

Resource Usage / Memory

autovacuum_work_mem	Sets the maximum memory to be used by each autovacuum worker process.
dynamic_shared_memory_type	Selects the dynamic shared memory implementation used.
huge_pages	Use of huge pages on Linux.
maintenance_work_mem	Sets the maximum memory to be used for maintenance operations.
max_prepared_transactions	Sets the maximum number of simultaneously prepared transactions.
max_stack_depth	Sets the maximum stack depth, in kilobytes.
replacement_sort_tuples	Sets the maximum number of tuples to be sorted using replacement selection.
shared_buffers	Sets the number of shared memory buffers used by the server.
temp_buffers	Sets the maximum number of temporary buffers used by each session.
track_activity_query_size	Sets the size reserved for pg_stat_activity.query, in bytes.
work_mem	Sets the maximum memory to be used for query workspaces.

Statistics / Monitoring

Postgresql Commands Cheat Sheet Cheat

log_executor_stats	Writes executor performance statistics to the server log.
log_parser_stats	Writes parser performance statistics to the server log.
log_planner_stats	Writes planner performance statistics to the server log.
log_statement_stats	Writes cumulative performance statistics to the server log.

Statistics / Query and Index Statistics Collector

stats_temp_directory	Writes temporary statistics files to the specified directory.
track_activities	Collects information about executing commands.
track_counts	Collects statistics on database activity.
track_functions	Collects function-level statistics on database activity.
track_io_timing	Collects timing statistics for database I/O activity.

Version and Platform Compatibility / Other Platforms and Clients

transform_null_equals

Treats “expr=NULL” as “expr IS NULL”.

Version and Platform Compatibility / Previous PostgreSQL Versions

array_nulls	Enable input of NULL elements in arrays.
backslash_quote	Sets whether “'” is allowed in string literals.
default_with_oids	Create new tables with OIDs by default.
escape_string_warning	Warn about backslash escapes in ordinary string literals.
lo_compat_privileges	Enables backward compatibility mode for privilege checks on large objects.
operator_precedence_warning	Emit a warning for constructs that changed meaning since PostgreSQL 9.4.
quote_all_identifiers	When generating SQL fragments, quote all identifiers.
standard_conforming_strings	Causes ‘…’ strings to treat backslashes literally.
synchronize_seqscans	Enable synchronized sequential scans.

Write-Ahead Log / Archiving

archive_command	Sets the shell command that will be called to archive a WAL file.
archive_mode	Allows archiving of WAL files using archive_command.
archive_timeout	Forces a switch to the next WAL file if a new file has not been started within N seconds.

Write-Ahead Log / Checkpoints

checkpoint_completion_target	Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
checkpoint_flush_after	Number of pages after which previously performed writes are flushed to disk.
checkpoint_timeout	Sets the maximum time between automatic WAL checkpoints.
checkpoint_warning	Enables warnings if checkpoint segments are filled more frequently than this.
max_wal_size	Sets the WAL size that triggers a checkpoint.
min_wal_size	Sets the minimum size to shrink the WAL to.

Write-Ahead Log / Settings

commit_delay	Sets the delay in microseconds between transaction commit and flushing WAL to disk.
commit_siblings	Sets the minimum concurrent open transactions before performing commit_delay.
fsync	Forces synchronization of updates to disk.
full_page_writes	Writes full pages to WAL when first modified after a checkpoint.
synchronous_commit	Sets the current transaction’s synchronization level.
wal_buffers	Sets the number of disk-page buffers in shared memory for WAL.
wal_compression	Compresses full-page writes written in WAL file.
wal_level	Set the level of information written to the WAL.
wal_log_hints	Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modifications.
wal_sync_method	Selects the method used for forcing WAL updates to disk.
wal_writer_delay	Time between WAL flushes performed in the WAL writer.
wal_writer_flush_after	Amount of WAL written out by WAL writer that triggers a flush.

About pgDash

pgDash is a modern, in-depth monitoring solution designedspecifically for PostgreSQL deployments. pgDash shows you information andmetrics about every aspect of your PostgreSQL database server, collected usingthe open-source tool pgmetrics.

pgDash provides core reporting and visualizationfunctionality, including collecting and displaying PostgreSQL information andproviding time-series graphs, detailed reports, alerting, teams and more.

Starting the server

Windows Users

You must do this every time you restart your machine.

Mac Users

After you have run this command the first time, postgres will start automatically whenever you boot up your computer.

Starting the postgres shell

Open the shell and connect to the city_explorer database:

psql city_explorer

With no arguments, psql will start the shell connected to your user-default database.

Creating a database

This will create a new database called “book_wiki”, which can contain many individual tables:

CREATE DATABASE book_wiki;

Quit the postgres shell

Type the backslash character, immediatly followed by a “q”:

yourname=# q

Basic shell navigation

List all of your databases

yourname=# l

Connect to a database

yourname=# c book_wiki

List all tables (aka: relations) in the database

yourname=# dt

The command dt means “describe table”.

Display the schema for a table

yourname=# d table-name

Troubleshooting Tips

Error - psql not connected to server

psql: could not connect to server: No such file or directory

Postgresql Commands Cheat Sheet Download

Solution - Turn Postgres On

See above notes about starting the server.

Error - schema.sql file not found

When connecting your schema.sql to your database:

schema.sql: No such file or directory

Postgresql Commands Cheat Sheet Free

Solution

You must specify the full path to the .sql file or run the command from the same directory where the .sql file lives.