An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. It is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL systems in production environments that manage in excess of 4 terabytes of data. Some general PostgreSQL limits are included in the table below.
PostgreSQL has won praise from its users and industry recognition, including the Linux New Media Award for Best Database System and five time winner of the The Linux Journal Editors' Choice Award for best DBMS. Featureful and Standards CompliantPostgreSQL prides itself in standards compliance. Its SQL implementation strongly conforms to the ANSI-SQL:2008 standard. It has full support for subqueries (including subselects in the FROM clause), read-committed and serializable transaction isolation levels. And while PostgreSQL has a fully relational system catalog which itself supports multiple schemas per database, its catalog is also accessible through the Information Schema as defined in the SQL standard. Data integrity features include (compound) primary keys, foreign keys with restricting and cascading updates/deletes, check constraints, unique constraints, and not null constraints. It also has a host of extensions and advanced features. Among the conveniences are auto-increment columns through sequences, and LIMIT/OFFSET allowing the return of partial result sets. PostgreSQL supports compound, unique, partial, and functional indexes which can use any of its B-tree, R-tree, hash, or GiST storage methods. GiST (Generalized Search Tree) indexing is an advanced system which brings together a wide array of different sorting and searching algorithms including B-tree, B+-tree, R-tree, partial sum trees, ranked B+-trees and many others. It also provides an interface which allows both the creation of custom data types as well as extensible query methods with which to search them. Thus, GiST offers the flexibility to specify what you store, how you store it, and the ability to define new ways to search through it --- ways that far exceed those offered by standard B-tree, R-tree and other generalized search algorithms. GiST serves as a foundation for many public projects that use PostgreSQL such as OpenFTS and PostGIS. OpenFTS (Open Source Full Text Search engine) provides online indexing of data and relevance ranking for database searching. PostGIS is a project which adds support for geographic objects in PostgreSQL, allowing it to be used as a spatial database for geographic information systems (GIS), much like ESRI's SDE or Oracle's Spatial extension. Other advanced features include table inheritance, a rules systems, and database events. Table inheritance puts an object oriented slant on table creation, allowing database designers to derive new tables from other tables, treating them as base classes. Even better, PostgreSQL supports both single and multiple inheritance in this manner. The rules system, also called the query rewrite system, allows the database designer to create rules which identify specific operations for a given table or view, and dynamically transform them into alternate operations when they are processed. The events system is an interprocess communication system in which messages and events can be transmitted between clients using the LISTEN and NOTIFY commands, allowing both simple peer to peer communication and advanced coordination on database events. Since notifications can be issued from triggers and stored procedures, PostgreSQL clients can monitor database events such as table updates, inserts, or deletes as they happen. Highly CustomizablePostgreSQL runs stored procedures in more than a dozen programming languages, including Java, Perl, Python, Ruby, Tcl, C/C++, and its own PL/pgSQL, which is similar to Oracle's PL/SQL. Included with its standard function library are hundreds of built-in functions that range from basic math and string operations to cryptography and Oracle compatibility. Triggers and stored procedures can be written in C and loaded into the database as a library, allowing great flexibility in extending its capabilities. Similarly, PostgreSQL includes a framework that allows developers to define and create their own custom data types along with supporting functions and operators that define their behavior. As a result, a host of advanced data types have been created that range from geometric and spatial primitives to network addresses to even ISBN/ISSN (International Standard Book Number/International Standard Serial Number) data types, all of which can be optionally added to the system. Just as there are many procedure languages supported by PostgreSQL, there are also many library interfaces as well, allowing various languages both compiled and interpreted to interface with PostgreSQL. There are interfaces for Java (JDBC), ODBC, Perl, Python, Ruby, C, C++, PHP, Lisp, Scheme, and Qt just to name a few. Best of all, PostgreSQL's source code is available under a liberal open source license: the PostgreSQL License. This license gives you the freedom to use, modify and distribute PostgreSQL in any form you like, open or closed source. Any modifications, enhancements, or changes you make are yours to do with as you please. As such, PostgreSQL is not only a powerful database system capable of running the enterprise, it is a development platform upon which to develop in-house, web, or commercial software products that require a capable RDBMS. | System Catalogs pg_aggregate pg_am pg_amop pg_amproc pg_attrdef pg_attribute pg_authid pg_auth_members pg_cast pg_class pg_constraint pg_collation pg_conversion pg_database pg_db_role_setting pg_default_acl pg_depend pg_description pg_enum pg_extension pg_foreign_data_wrapper pg_foreign_server pg_foreign_table pg_index pg_inherits pg_language pg_largeobject pg_largeobject_metadata pg_namespace pg_opclass pg_operator pg_opfamily pg_pltemplate pg_proc pg_rewrite pg_seclabel pg_shdepend pg_shdescription pg_statistic pg_tablespace pg_trigger pg_ts_config pg_ts_config_map pg_ts_dict pg_ts_parser pg_ts_template pg_type pg_user_mapping System Views pg_available_extension_versions pg_cursors pg_group pg_indexes pg_locks pg_prepared_statements pg_prepared_xacts pg_roles pg_rules pg_seclabels pg_settings pg_shadow pg_stats pg_tables pg_timezone_abbrevs pg_timezone_names pg_user pg_user_mappings pg_views |
PostgreSQL DBA Routine Tasks
pgpool vs slony
posted Feb 24, 2012, 4:27 PM by Sachchida Ojha [ updated Feb 24, 2012, 4:32 PM ]
Slony-I is a "master to multiple slaves" replication system for PostgreSQL supporting cascading (e.g. - a node can feed another node which feeds another node...) and failover. The big picture for the development of Slony-I is that it is a master-slave replication system that includes all features and capabilities needed to replicate large databases to a reasonably limited number of slave systems. Slony-I is a system designed for use at data centers and backup sites, where the normal mode of operation is that all nodes are available. PostgreSQL 9.0 includes streaming replication, which, for a number of use cases, is likely to be simpler and more convenient than Slony-I. There are, however, three characteristic kinds of cases where you'll need something like Slony-I, where the built-in WAL-based replication won't work: You need to interact between PostgreSQL versions. Slony (and similar systems like Londiste and Bucardo) can cope with having nodes running different versions of PostgreSQL.
pgpool-II is a middleware that works between PostgreSQL servers and a PostgreSQL database client. It is licensed under BSD license. It provides the following features. Connection Pooling pgpool-II saves connections to the PostgreSQL servers, and reuse them whenever a new connection with the same properties (i.e. username, database, protocol version) comes in. It reduces connection overhead, and improves system's overall throughput. Replication pgpool-II can manage multiple PostgreSQL servers. Using the replication function enables creating a realtime backup on 2 or more physical disks, so that the service can continue without stopping servers in case of a disk failure. Load Balance If a database is replicated, executing a SELECT query on any server will return the same result. pgpool-II takes an advantage of the replication feature to reduce the load on each PostgreSQL server by distributing SELECT queries among multiple servers, improving system's overall throughput. At best, performance improves proportionally to the number of PostgreSQL servers. Load balance works best in a situation where there are a lot of users executing many queries at the same time. Limiting Exceeding Connections There is a limit on the maximum number of concurrent connections with PostgreSQL, and connections are rejected after this many connections. Setting the maximum number of connections, however, increases resource consumption and affect system performance. pgpool-II also has a limit on the maximum number of connections, but extra connections will be queued instead of returning an error immediately. Parallel Query Using the parallel query function, data can be divided among the multiple servers, so that a query can be executed on all the servers concurrently to reduce the overall execution time. Parallel query works the best when searching large-scale data. pgpool-II talks PostgreSQL's backend and frontend protocol, and relays a connection between them. Therefore, a database application (frontend) thinks that pgpool-II is the actual PostgreSQL server, and the server (backend) sees pgpool-II as one of its clients. Because pgpool-II is transparent to both the server and the client, an existing database application can be used with pgpool-II almost without a change to its sources. |
What is Pgpool ? how it is implemented?
posted Feb 24, 2012, 5:59 AM by Sachchida Ojha [ updated Feb 24, 2012, 4:25 PM ]
Hot Standby/Streaming Replication Vs Warm Standby/Log Shipping
posted Feb 23, 2012, 6:15 PM by Sachchida Ojha
Hot Standby/Streaming Replication is available as of PostgreSQL 9.0 and provides asynchronous binary replication to one or more standbys. Standbys may also become hot standbys meaning they can be queried as a read-only database. This is the fastest type of replication available as WAL data is sent immediately rather than waiting for a whole segment to be produced and shipped. Warm Standby/Log Shipping is a HA solution which 'replicates' a database cluster to an archive or a warm (can be brought up quickly, but not available for querying) standby server. Overhead is very low and it's easy to set up. This is a simple and appropriate solution if all you care about is continuous backup and short failover times. |
Object level permission in PostgreSQL
posted Feb 20, 2012, 6:33 AM by Sachchida Ojha
Every object (tables, views and sequences) have an owner, which is
the person that created it. The owner, or a superuser, can set
permissions on the object. Permissions are made up of a user or
group name and a set of rights. These rights described in the
table below.
You can apply these privileges to users, groups or a special target called PUBLIC, which is any user on the system. Viewing privileges You can view permissions using the \z command in psql. You can use \d to view the owner. dbaref=# \dp Adding privileges You can assign privileges using the GRANT command. GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] The WITH GRANT OPTION allows you give the person you are granting the privileges the ability to grant that privilege themselves. We can give bob the ability to make any changes to the data in suppliers using: GRANT INSERT, UPDATE, DELETE ON TABLE suppliers TO bob; Removing privileges You can also remove privileges using the REVOKE which has the same syntax as the GRANT.REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ] GRANT OPTION FOR allows you to remove the ability to grant privileges to others, and not the privileges themselves. Suppose you want to remove privileges from bob, and anyone he has granted it to, we can use the CASCADE option. REVOKE INSERT UPDATE DELETE ON TABLE suppliers FROM bob CASCASE Column Level Privileges PostgreSQL doesn't directly support privileges at the column level but you can fake the, using views. To do this, you create a view with all the columns you want that person to see and grant them privileges to view that view. Changing Ownership It is possible to change the ownership of objects using the ALTER TABLE: ALTER TABLE suppliers OWNER TO bob; This can be time consuming to do if you have a lot of tables. A quicker, but possibly dodgy way to fix this is to use the following untested SQL command. You need to set relowner to the sysid of the new owner, which you can find by checking pg_shadow. UPDATE pg_class SET relowner = 100 WHERE pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = 'public'; |
postmaster -- PostgreSQL multiuser database server
posted Feb 19, 2012, 6:40 PM by Sachchida Ojha
postmaster is the PostgreSQL multiuser database server. In
order for a client application to access a database it connects
(over a network or locally) to a running postmaster. The postmaster then starts a separate server process
("postgres") to handle the
connection. The postmaster also manages
the communication among server processes.
[root@usha data]# ls -l total 92 drwx------. 5 postgres postgres 4096 Feb 19 13:00 base drwx------. 2 postgres postgres 4096 Feb 19 21:14 global drwx------. 2 postgres postgres 4096 Feb 19 13:00 pg_clog -rw-------. 1 postgres postgres 4232 Feb 19 13:00 pg_hba.conf -rw-------. 1 postgres postgres 1636 Feb 19 13:00 pg_ident.conf drwx------. 2 postgres postgres 4096 Feb 19 13:30 pg_log drwx------. 4 postgres postgres 4096 Feb 19 13:00 pg_multixact drwx------. 2 postgres postgres 4096 Feb 19 13:30 pg_notify drwx------. 2 postgres postgres 4096 Feb 19 13:00 pg_serial drwx------. 2 postgres postgres 4096 Feb 19 21:33 pg_stat_tmp drwx------. 2 postgres postgres 4096 Feb 19 13:00 pg_subtrans drwx------. 2 postgres postgres 4096 Feb 19 13:00 pg_tblspc drwx------. 2 postgres postgres 4096 Feb 19 13:00 pg_twophase -rw-------. 1 postgres postgres 4 Feb 19 13:00 PG_VERSION drwx------. 3 postgres postgres 4096 Feb 19 13:00 pg_xlog -rw-------. 1 postgres postgres 19146 Feb 19 13:00 postgresql.conf -rw-------. 1 postgres postgres 71 Feb 19 13:30 postmaster.opts -rw-------. 1 postgres postgres 81 Feb 19 13:30 postmaster.pid [root@usha data]# cat postmaster.pid 11987 /var/lib/pgsql/9.1/data 1329676222 5432 /tmp localhost 5432001 48234497 [root@usha data]# Synopsis postmaster [-A 0 | 1 ] [-B nbuffers] [-c name=value] [-d debug-level] [-D datadir] [-F] [-h hostname] [-i] [-k directory] [-l] [-N max-connections] [-o extra-options] [-p port] [-S] [--name=value] [-n | -s] By default the postmaster starts in the foreground and prints log messages to the standard error stream. In practical applications the postmaster should be started as a background process, perhaps at boot time.One postmaster always manages the data from exactly one database cluster. A database cluster is a collection of databases that is stored at a common file system location (the "data area"). More than one postmaster process can run on a system at one time, so long as they use different data areas and different communication ports (see below). A data area is created with initdb. When the postmaster starts it needs to know the location of the data area. The location must be specified by the -D option or the PGDATA environment variable; there is no default. Typically, -D or PGDATA points directly to the data area directory created by initdb. Options |
Configuring PostgreSQL
posted Feb 19, 2012, 6:23 PM by Sachchida Ojha
postgresql.conf is the main configuration file for the PostgreSQL DBMS. It will be located in your data directory.
[root@usha local]# cd /var/lib/pgsql/9.1/data/ [root@usha data]# ls -ltr total 92 -rw-------. 1 postgres postgres 4 Feb 19 13:00 PG_VERSION drwx------. 2 postgres postgres 4096 Feb 19 13:00 pg_twophase drwx------. 2 postgres postgres 4096 Feb 19 13:00 pg_tblspc drwx------. 2 postgres postgres 4096 Feb 19 13:00 pg_serial drwx------. 4 postgres postgres 4096 Feb 19 13:00 pg_multixact -rw-------. 1 postgres postgres 19146 Feb 19 13:00 postgresql.conf -rw-------. 1 postgres postgres 1636 Feb 19 13:00 pg_ident.conf -rw-------. 1 postgres postgres 4232 Feb 19 13:00 pg_hba.conf drwx------. 3 postgres postgres 4096 Feb 19 13:00 pg_xlog drwx------. 2 postgres postgres 4096 Feb 19 13:00 pg_subtrans drwx------. 2 postgres postgres 4096 Feb 19 13:00 pg_clog drwx------. 5 postgres postgres 4096 Feb 19 13:00 base -rw-------. 1 postgres postgres 81 Feb 19 13:30 postmaster.pid drwx------. 2 postgres postgres 4096 Feb 19 13:30 pg_notify -rw-------. 1 postgres postgres 71 Feb 19 13:30 postmaster.opts drwx------. 2 postgres postgres 4096 Feb 19 13:30 pg_log drwx------. 2 postgres postgres 4096 Feb 19 13:30 global drwx------. 2 postgres postgres 4096 Feb 19 21:11 pg_stat_tmp [root@usha data]# # ----------------------------- # PostgreSQL configuration file # ----------------------------- # # This file consists of lines of the form: # # name = value # # (The "=" is optional.) Whitespace may be used. Comments are introduced with # "#" anywhere on a line. The complete list of parameter names and allowed # values can be found in the PostgreSQL documentation. # # The commented-out settings shown in this file represent the default values. # Re-commenting a setting is NOT sufficient to revert it to the default value; # you need to reload the server. # # This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, or use "pg_ctl reload". Some # parameters, which are marked below, require a server shutdown and restart to # take effect. # # Any parameter can also be given as a command-line option to the server, e.g., # "postgres -c log_connections=on". Some parameters can be changed at run time # with the "SET" SQL command. # # Memory units: kB = kilobytes Time units: ms = milliseconds # MB = megabytes s = seconds # GB = gigabytes min = minutes # h = hours # d = days #------------------------------------------------------------------------------ # FILE LOCATIONS #------------------------------------------------------------------------------ # The default values of these variables are driven from the -D command-line # option or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory # (change requires restart) #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file # (change requires restart) #ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file # (change requires restart) # If external_pid_file is not explicitly set, no extra PID file is written. #external_pid_file = '(none)' # write an extra PID file # (change requires restart) #------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all # (change requires restart) #port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) # Note: Increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). #superuser_reserved_connections = 3 # (change requires restart) #unix_socket_directory = '' # (change requires restart) #unix_socket_group = '' # (change requires restart) #unix_socket_permissions = 0777 # begin with 0 to use octal notation # (change requires restart) #bonjour = off # advertise server via Bonjour # (change requires restart) #bonjour_name = '' # defaults to the computer name # (change requires restart) # - Security and Authentication - #authentication_timeout = 1min # 1s-600s #ssl = off # (change requires restart) #ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers # (change requires restart) #ssl_renegotiation_limit = 512MB # amount of data between renegotiations #password_encryption = on #db_user_namespace = off # Kerberos and GSSAPI #krb_server_keyfile = '' #krb_srvname = 'postgres' # (Kerberos only) #krb_caseins_users = off # - TCP Keepalives - # see "man 7 tcp" for details #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default #------------------------------------------------------------------------------ # RESOURCE USAGE (except WAL) #------------------------------------------------------------------------------ # - Memory - shared_buffers = 32MB # min 128kB # (change requires restart) #temp_buffers = 8MB # min 800kB #max_prepared_transactions = 0 # zero disables the feature # (change requires restart) # Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). # It is not advisable to set max_prepared_transactions nonzero unless you # actively intend to use prepared transactions. #work_mem = 1MB # min 64kB #maintenance_work_mem = 16MB # min 1MB #max_stack_depth = 2MB # min 100kB # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 # (change requires restart) #shared_preload_libraries = '' # (change requires restart) # - Cost-Based Vacuum Delay - #vacuum_cost_delay = 0ms # 0-100 milliseconds #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 1-10000 credits # - Background Writer - #bgwriter_delay = 200ms # 10-10000ms between rounds #bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round #bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round # - Asynchronous Behavior - #effective_io_concurrency = 1 # 1-1000. 0 disables prefetching #------------------------------------------------------------------------------ # WRITE AHEAD LOG #------------------------------------------------------------------------------ # - Settings - #wal_level = minimal # minimal, archive, or hot_standby # (change requires restart) #fsync = on # turns forced synchronization on or off #synchronous_commit = on # synchronization level; on, off, or local #wal_sync_method = fsync # the default is the first option # supported by the operating system: # open_datasync # fdatasync (default on Linux) # fsync # fsync_writethrough # open_sync #full_page_writes = on # recover from partial page writes #wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers # (change requires restart) #wal_writer_delay = 200ms # 1-10000 milliseconds #commit_delay = 0 # range 0-100000, in microseconds #commit_siblings = 5 # range 1-1000 # - Checkpoints - #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 5min # range 30s-1h #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 #checkpoint_warning = 30s # 0 disables # - Archiving - #archive_mode = off # allows archiving to be done # (change requires restart) #archive_command = '' # command to use to archive a logfile segment #archive_timeout = 0 # force a logfile segment switch after this # number of seconds; 0 disables #------------------------------------------------------------------------------ # REPLICATION #------------------------------------------------------------------------------ # - Master Server - # These settings are ignored on a standby server #max_wal_senders = 0 # max number of walsender processes # (change requires restart) #wal_sender_delay = 1s # walsender cycle time, 1-10000 milliseconds #wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed #replication_timeout = 60s # in milliseconds; 0 disables #synchronous_standby_names = '' # standby servers that provide sync rep # comma-separated list of application_name # from standby(s); '*' = all # - Standby Servers - # These settings are ignored on a master server #hot_standby = off # "on" allows queries during recovery # (change requires restart) #max_standby_archive_delay = 30s # max delay before canceling queries # when reading WAL from archive; # -1 allows indefinite delay #max_standby_streaming_delay = 30s # max delay before canceling queries # when reading streaming WAL; # -1 allows indefinite delay #wal_receiver_status_interval = 10s # send replies at least this often # 0 disables #hot_standby_feedback = off # send info from standby to prevent # query conflicts #------------------------------------------------------------------------------ # QUERY TUNING #------------------------------------------------------------------------------ # - Planner Method Configuration - #enable_bitmapscan = on #enable_hashagg = on #enable_hashjoin = on #enable_indexscan = on #enable_material = on #enable_mergejoin = on #enable_nestloop = on #enable_seqscan = on #enable_sort = on #enable_tidscan = on # - Planner Cost Constants - #seq_page_cost = 1.0 # measured on an arbitrary scale #random_page_cost = 4.0 # same scale as above #cpu_tuple_cost = 0.01 # same scale as above #cpu_index_tuple_cost = 0.005 # same scale as above #cpu_operator_cost = 0.0025 # same scale as above #effective_cache_size = 128MB # - Genetic Query Optimizer - #geqo = on #geqo_threshold = 12 #geqo_effort = 5 # range 1-10 #geqo_pool_size = 0 # selects default based on effort #geqo_generations = 0 # selects default based on effort #geqo_selection_bias = 2.0 # range 1.5-2.0 #geqo_seed = 0.0 # range 0.0-1.0 # - Other Planner Options - #default_statistics_target = 100 # range 1-10000 #constraint_exclusion = partition # on, off, or partition #cursor_tuple_fraction = 0.1 # range 0.0-1.0 #from_collapse_limit = 8 #join_collapse_limit = 8 # 1 disables collapsing of explicit # JOIN clauses #------------------------------------------------------------------------------ # ERROR REPORTING AND LOGGING #------------------------------------------------------------------------------ # - Where to Log - log_destination = 'stderr' # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. # This is used when logging to stderr: logging_collector = on # Enable capturing of stderr and csvlog # into log files. Required to be on for # csvlogs. # (change requires restart) # These are only used if logging_collector is on: log_directory = 'pg_log' # directory where log files are written, # can be absolute or relative to PGDATA log_filename = 'postgresql-%a.log' # log file name pattern, # can include strftime() escapes #log_file_mode = 0600 # creation mode for log files, # begin with 0 to use octal notation log_truncate_on_rotation = on # If on, an existing log file with 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 # off, meaning append to existing files # in all cases. log_rotation_age = 1d # Automatic rotation of logfiles will # happen after that time. 0 disables. log_rotation_size = 0 # Automatic rotation of logfiles will # happen after that much log output. # 0 disables. # These are relevant when logging to syslog: #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' #silent_mode = off # Run server silently. # DO NOT USE without syslog or # logging_collector # (change requires restart) # - When to Log - #client_min_messages = notice # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # log # notice # warning # error #log_min_messages = warning # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic #log_min_error_statement = error # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic (effectively off) #log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements # and their durations, > 0 logs only # statements running at least this number # of milliseconds # - What to Log - #debug_print_parse = off #debug_print_rewritten = off #debug_print_plan = off #debug_pretty_print = on #log_checkpoints = off #log_connections = off #log_disconnections = off #log_duration = off #log_error_verbosity = default # terse, default, or verbose messages #log_hostname = off #log_line_prefix = '' # special values: # %a = application name # %u = user name # %d = database name # %r = remote host and port # %h = remote host # %p = process ID # %t = timestamp without milliseconds # %m = timestamp with milliseconds # %i = command tag # %e = SQL state # %c = session ID # %l = session line number # %s = session start timestamp # %v = virtual transaction ID # %x = transaction ID (0 if none) # %q = stop here in non-session # processes # %% = '%' # e.g. '<%u%%%d> ' #log_lock_waits = off # log lock waits >= deadlock_timeout #log_statement = 'none' # none, ddl, mod, all #log_temp_files = -1 # log temporary files equal or larger # than the specified size in kilobytes; # -1 disables, 0 logs all temp files #log_timezone = '(defaults to server environment setting)' #------------------------------------------------------------------------------ # RUNTIME STATISTICS #------------------------------------------------------------------------------ # - Query/Index Statistics Collector - #track_activities = on #track_counts = on #track_functions = none # none, pl, all #track_activity_query_size = 1024 # (change requires restart) #update_process_title = on #stats_temp_directory = 'pg_stat_tmp' # - Statistics Monitoring - #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off #------------------------------------------------------------------------------ # AUTOVACUUM PARAMETERS #------------------------------------------------------------------------------ #autovacuum = on # Enable autovacuum subprocess? 'on' # requires track_counts to also be on. #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and # their durations, > 0 logs only # actions running at least this number # of milliseconds. #autovacuum_max_workers = 3 # max number of autovacuum subprocesses # (change requires restart) #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum #autovacuum_analyze_threshold = 50 # min number of row updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit #------------------------------------------------------------------------------ # CLIENT CONNECTION DEFAULTS #------------------------------------------------------------------------------ # - Statement Behavior - #search_path = '"$user",public' # schema names #default_tablespace = '' # a tablespace name, '' uses the default #temp_tablespaces = '' # a list of tablespace names, '' uses # only default tablespace #check_function_bodies = on #default_transaction_isolation = 'read committed' #default_transaction_read_only = off #default_transaction_deferrable = off #session_replication_role = 'origin' #statement_timeout = 0 # in milliseconds, 0 is disabled #vacuum_freeze_min_age = 50000000 #vacuum_freeze_table_age = 150000000 #bytea_output = 'hex' # hex, escape #xmlbinary = 'base64' #xmloption = 'content' # - Locale and Formatting - datestyle = 'iso, mdy' #intervalstyle = 'postgres' #timezone = '(defaults to server environment setting)' #timezone_abbreviations = 'Default' # Select the set of available time zone # abbreviations. Currently, there are # Default # Australia # India # You can create your own file in # share/timezonesets/. #extra_float_digits = 0 # min -15, max 3 #client_encoding = sql_ascii # actually, defaults to database # encoding # These settings are initialized by initdb, but they can be changed. lc_messages = 'en_US.UTF-8' # locale for system error message # strings lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting # default configuration for text search default_text_search_config = 'pg_catalog.english' # - Other Defaults - #dynamic_library_path = '$libdir' #local_preload_libraries = '' #------------------------------------------------------------------------------ # LOCK MANAGEMENT #------------------------------------------------------------------------------ #deadlock_timeout = 1s #max_locks_per_transaction = 64 # min 10 # (change requires restart) # Note: Each lock table slot uses ~270 bytes of shared memory, and there are # max_locks_per_transaction * (max_connections + max_prepared_transactions) # lock table slots. #max_pred_locks_per_transaction = 64 # min 10 # (change requires restart) #------------------------------------------------------------------------------ # VERSION/PLATFORM COMPATIBILITY #------------------------------------------------------------------------------ # - Previous PostgreSQL Versions - #array_nulls = on #backslash_quote = safe_encoding # on, off, or safe_encoding #default_with_oids = off #escape_string_warning = on #lo_compat_privileges = off #quote_all_identifiers = off #sql_inheritance = on #standard_conforming_strings = on #synchronize_seqscans = on # - Other Platforms and Clients - #transform_null_equals = off #------------------------------------------------------------------------------ # ERROR HANDLING #------------------------------------------------------------------------------ #exit_on_error = off # terminate session on any error? #restart_after_crash = on # reinitialize after backend crash? #------------------------------------------------------------------------------ # CUSTOMIZED OPTIONS #------------------------------------------------------------------------------ #custom_variable_classes = '' # list of custom variable class names [root@usha data]# |
Authentication and Authorisation
posted Feb 19, 2012, 6:02 PM by Sachchida Ojha [ updated Feb 19, 2012, 6:02 PM ]
PostgreSQL has two levels of authorisation, one at the database level,
called host based authentication, and one at a finer level on tables,
views and sequences.
Host-Based Authentication using pg_hba.conf The host-based authentication is controlled by the pg_hba.conf file and defines which users can connect to which database and how they can connect to it. The file is a list of declarations, which are searched in order until one of the lines match. They list the access method, the database they are trying to connect to, the user trying to connect and the authentication method being used. The general format of the pg_hba.conf file is a set of records, one per line. Blank lines are ignored, as is any text after the # comment character. A record is made up of a number of fields which are separated by spaces and/or tabs. Fields can contain white space if the field value is quoted. Records cannot be continued across lines. Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied. A record can have one of the seven formats ========================================================================================= local database user auth-method [auth-option] host database user CIDR-address auth-method [auth-option] hostssl database user CIDR-address auth-method [auth-option] hostnossl database user CIDR-address auth-method [auth-option] host database user IP-address IP-mask auth-method [auth-option] hostssl database user IP-address IP-mask auth-method [auth-option] hostnossl database user IP-address IP-mask auth-method [auth-option]========================================================================================= Access methods There are three different access methods: local This is for a user connecting via the unix socket on the local machine. A line for this method will be in the form:local DATABASE USER METHOD [OPTION] host This is matches connections over a TCP/IP network connection host DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION] host DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION] hostnossl, hostssl This is for users connecting over a non-encrypted or an encrypted TCP/IP connection using SSL. This is so that you can treat secure and non-secure connections differently. For example you might be happy to have clear text passwords over SSL, but only allow MD5 over non-secure connections. hostnossl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION] hostnossl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION] hostssl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION] hostssl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION] You can list several databases by separating them by commas. There are two special database names, all and sameuser. all allows the person to connect to all databases on the server. sameuser allows the user to connect to a database with the same name as the user connecting. You can also supply a filename which lists databases they can connect to by using @filename where filename is a file in the same directory as the pg_hba.conf. You can also list several users by separating them by commas. You can specify groups by prefixing the name with a +. Again you can use a filename with users in by using @filename where filename is a file in the same directory as pg_hba.conf. There is the special username all, which matches any user. Authentication Methods trust This method allows any user to connect without a password. This should be avoided unless you know what you are doing as it could be a security risk. reject This is the reverse of trust, as it rejects any one. This is particularly useful where you want to enable access to a range of addresses, but want to block a particular host in that range. host sales alice 10.0.0.128 255.255.255.255 reject host sales alice 10.0.0.0 255.255.0.0 md5 password This method allows someone to connect providing they have given the correct password for their user in the pg_shadow table. If the password field in that table is null, then they will be rejected. The password is sent in cleartext, so you probably only want to enable this for hostssl connections. crypt This is like password, except it is encrypted with the trivial unix crypt encryption. This is not a very strong encryption, so I suggest you don't use this. md5 This is like crypt and password, except it uses the stronger MD5 to encrypt the password. If you have to use non-ssl connections, I recommend you using this method. ident With network connections, this method uses the ident protocol (RFC1413) to check which user on the remote system owns the network connection being used to talk to the server. This is easy to spoof, so you shouldn't reply on this for unsecured networks. I would recommend against it on all networks. With local connections, it uses the unix user connecting to the unix socket and is much more secure. This allows local users to connect without a password. This is the only method that requires an option, which is the name of a map in pg_ident.conf, which maps remote users to postgresql users. The format of pg_ident.conf is: map unixuser postgresuser This allows you to have the same remote user map to different postgres users when they connect to different databases, There is a special map name called sameuser, which uses the same remote username for the postgresql name. krb4, krb5 These allow you to use kerberos authentication. pam This method allows you to authenticate users against the local system's pam (pluggable authentication modules) subsystem. For local connections, I would recommend ident, password, crypt or md5. For hostssl connections any of the password methods will work, but md5 is preferable. For host and hostnossl, I can only recommend md5 and hostssl should be used in preference to host and hostnossl. local all postgres ident sameuser hostssl all postgres 0.0.0.0 0.0.0.0 md5 local sameuser all ident sameuser hostssl sameuser all 10.0.0.0 255.255.255.0 md5 hostssl sales alice 10.0.0.1/32 md5 |
Installing PostgreSQL 9.1 on Fedora 15
posted Feb 19, 2012, 10:45 AM by Sachchida Ojha
1. Download the Software
http://yum.postgresql.org/repopackages.php
http://yum.postgresql.org/repopackages.php
1-8 of 8