WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the A option the next time you run initdb. Success. You can now start the database server using: postmaster -D /u/pg/data or pg_ctl -D /u/pg/data -l logfile start

LOG: database system was shut down at 2005-03-03 10:49:18 EST LOG: checkpoint record is at 0/A34010 LOG: redo record is at 0/A34010; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 545; next OID: 17233 LOG: database system is ready

manually pg_ctl on boot

Mastering PostgreSQL Administration

7

Stopping Postmaster

LOG: received smart shutdown request LOG: shutting down LOG: database system is shut down

----------------------------PostgreSQL configuration file ----------------------------This file consists of lines of the form: name = value (The ’=’ is optional.) White space may be used. Comments are introduced with ’#’ anywhere on a line. The complete list of option names and allowed values can be found in the PostgreSQL documentation. The commented-out settings shown in this file represent the default values.

Mastering PostgreSQL Administration

18

PostgreSQL.Conf (Continued)

# # # # # # # # # # # #

Please note that re-commenting a setting is NOT sufficient to revert it to the default value, unless you restart the postmaster. Any option can also be given as a command line switch to the postmaster, e.g. ’postmaster -c log_connections=on’. Some options can be changed at run-time with the ’SET’ SQL command. This file is read on postmaster startup and when the postmaster receives a SIGHUP. If you edit the file on a running system, you have to SIGHUP the postmaster for the changes to take effect, or use "pg_ctl reload". Some settings, such as listen_address, require a postmaster shutdown and restart to take effect.

# - Where to Log #log_destination = ’stderr’ # Valid values are combinations of stderr, # syslog and eventlog, depending on # platform. # This is relevant when logging to stderr: #redirect_stderr = false # Enable capturing of stderr into log files. # These are only relevant if redirect_stderr is true: #log_directory = ’pg_log’ # Directory where log files are written. # May be specified absolute or relative to PGDATA #log_filename = ’postgresql-%Y-%m-%d_%H%M%S.log’ # Log file name pattern. # May include strftime() escapes

Mastering PostgreSQL Administration

30

Error Reporting and Logging (Continued)

#log_truncate_on_rotation = false # If true, any existing log file of the # same name as the new log file will be truncated # rather than appended to. But such truncation # only occurs on time-driven rotation, # not on restarts or size-driven rotation. # Default is false, meaning append to existing # files in all cases. #log_rotation_age = 1440 # Automatic rotation of logfiles will happen after # so many minutes. 0 to disable. #log_rotation_size = 10240 # Automatic rotation of logfiles will happen after # so many kilobytes of log output. 0 to disable. # These are relevant when logging to syslog: #syslog_facility = ’LOCAL0’ #syslog_ident = ’postgres’

Recover table from previous backup, perhaps using pg_restore. It is possible to modify the backend code to make deleted tuples visible, dump out the deleted table and restore the original code. All tuples in the table since the previous vacuum will be visible. It is possible to restrict that so only tuples deleted by a speciﬁc transaction are visible.

Mastering PostgreSQL Administration

89

Write-Ahead Log (WAL) Corruption

See pg_resetxlog. Review recent transactions and identify any damage, including partially committed transactions.

Mastering PostgreSQL Administration

90

File Deletion

It may be necessary to create an empty ﬁle with the deleted ﬁle name so the object can be deleted, and then the object restored from backup.

Mastering PostgreSQL Administration

91

Accidental DROP TABLE

Restore from previous backup.

Mastering PostgreSQL Administration

92

Accidental DROP INDEX

Recreate index.

Mastering PostgreSQL Administration

93

Accidental DROP DATABASE

Restore from previous backup.

Mastering PostgreSQL Administration

94

Non-Starting Installation

Restart problems are usually caused by write-ahead log problems. See pg_resetxlog. Review recent transactions and identify any damage, including partially committed transactions.

Mastering PostgreSQL Administration

95

Index Corruption

Use

REINDEX.

Mastering PostgreSQL Administration

96

Table Corruption

Try reindexing the table. Try identifying the corrupt OID of the row and transfer the valid rows into another table using SELECT…INTO…WHERE oid != ###. Use http://sources.redhat.com/rhdb/tools.html to analyze the internal structure of the table.

Mastering PostgreSQL Administration

Description

This talk is designed for PostgreSQL administrators. It covers all aspects of PostgreSQL administration, including installation, security, file structure, configuration, reporting, backup, daily ma...

This talk is designed for PostgreSQL administrators. It covers all aspects of PostgreSQL administration, including installation, security, file structure, configuration, reporting, backup, daily maintenance, monitoring activity, disk space computations, and disaster recovery. It shows how to control host connectivity, configure the server, find the query being run by each session, and find the disk space used by each database.