https://wiki.postgresql.org/api.php?action=feedcontributions&user=Rstephan&feedformat=atomPostgreSQL wiki - User contributions [en]2024-03-19T02:13:48ZUser contributionsMediaWiki 1.35.13https://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=20762Simple Configuration Recommendation2013-09-13T13:20:39Z<p>Rstephan: </p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
These recommendations are to standardize and simplify PostgreSQL database configurations.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is easier to manage.<br />
<br />
The owner and group of the software, database files, and server processes should be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. An alternate and more manageable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones and FreeBSD jails or hypervisors like [http://en.wikipedia.org/wiki/Xen Xen] and [http://en.wikipedia.org/wiki/Kernel-based_Virtual_Machine KVM] make creation of multiple clusters within a single host unnecessary. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system snapshots. More on this later.<br />
|-<br />
|/pgcluster<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/pg_xlog.<br />
|-<br />
|/pglog<br />
|The location of the server log files.<br />
|-<br />
|/pgdata-system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata-temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata-''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. [http://en.wikipedia.org/wiki/ZFS ZFS] is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/current/static/continuous-archiving.html Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual. <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
File system delegated administration is an advantage for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=20761Simple Configuration Recommendation2013-09-13T13:18:18Z<p>Rstephan: </p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
These recommendations are to standardize and simplify PostgreSQL database configurations.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is easier to manage.<br />
<br />
The owner and group of the software, database files, and server processes should be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. An alternate and more manageable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones and FreeBSD jails or hypervisors like [http://en.wikipedia.org/wiki/Xen Xen] and [http://en.wikipedia.org/wiki/Kernel-based_Virtual_Machine KVM] make creation of multiple clusters within a single host unnecessary. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system snapshots. More on this later.<br />
|-<br />
|/pgcluster<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pglog<br />
|The location of the server log files.<br />
|-<br />
|/pgdata-system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata-temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata-''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. [http://en.wikipedia.org/wiki/ZFS ZFS] is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/current/static/continuous-archiving.html Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual. <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
File system delegated administration is an advantage for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Database_Schema_Recommendations_for_an_Application&diff=19080Database Schema Recommendations for an Application2013-02-22T17:46:51Z<p>Rstephan: </p>
<hr />
<div>==Database Schema Recommendations for an Application==<br />
<br />
Within a PostgreSQL database cluster the basic methods for separating and name spacing objects is through [http://www.postgresql.org/docs/current/static/manage-ag-overview.html Managing Databases] and [http://www.postgresql.org/docs/current/static/ddl-schemas.html Schema Data Definitions].<br />
<br />
The recommendation is to create a single database with multiple named schemas. This is different than a common (and older) practice of creating multiple databases and storing the objects within the "public" schema. Additionally, it is recommended to remove the public schema.<br />
<br />
These are some of the advantages to following this recommendation:<br />
<br />
* Cross schema object access is possible from a single database connection.<br />
* Granting access to a schema is executed through a GRANT statement versus a reconfiguration of the pg_hba.conf file.<br />
* Schemas are the ANSI standard for object separation and name spacing.<br />
* Managing only one database within a single server (PostgreSQL cluster).<br />
<br />
An advantage to using the separate database method is sessions connected to a database cannot cross database boundaries. Separation of objects is more complete. However, if the desire is have more separation of the objects (i.e., different environments), it is recommended to create a new server.<br />
<br />
Note: Seperation at the database level can be used for multi-tenant hosting when combined with the db_user_namespace configuration parameter. Schema seperation again becomes the important feature for seperating the database components within each of the hosted databases.<br />
<br />
===Creating Tablespaces===<br />
<br />
Create a set of tablespaces appropriate for each application. This provides for the physical separation of storage from one application to another. It also can provide for separation of storage within an application. Different storage of tables and indexes would enhance management flexibility and could provide additional performance.<br />
<br />
PostgreSQL expects the host operating system to provide device management. Create a file system for each tablespace to separate and control the tablespace storage. The following is an example using ZFS with storage pools named pgdatapool and pgindexpool. Two 10 GB tablespaces will be used for an application, one for tables and one for indexes.<br />
<br />
Unix commands:<br />
<br />
zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data<br />
zfs set refquota=10G pgdatapool/app1_data<br />
zfs set refreference=10G pgdatapool/app1_data<br />
zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index<br />
zfs set refquota=10G pgindexpool/app1_index<br />
zfs set refreference=10G pgindexpool/app1_index<br />
<br />
SQL commands:<br />
<br />
create tablespace app1_data location '/pgdata/app1_data';<br />
create tablespace app1_index location '/pgdata/app1_index';<br />
<br />
===Creating Accounts and Roles===<br />
<br />
PostgreSQL database objects have an account (role) ownership. Create an account synonymous with the schema that will name space the objects. This account will be used as the owner for the objects. For security purposes do not allow anyone to connect to the account owning the application database objects. This account will need access to the tablespaces previously created. When application objects are created make sure the ownership is properly set.<br />
<br />
create user app1 nologin;<br />
grant create on tablespace app1_data to app1;<br />
grant create on tablespace app1_index to app1;<br />
<br />
Grant database object privileges to users or application accounts through the use of roles. Create a set of roles appropriate for an application. For simplicity create at least two roles, one to use for read/write privileges for the application and one for query privileges. As database objects are created for the application perform the appropriate grants to the roles.<br />
<br />
create role app1_role;<br />
create role app1_query_role;<br />
<br />
Applications generally connect to the database using pooled (shared) accounts from application servers. Limit the knowledge of those accounts and their credentials. If possible, use definitions within the pg_hba.conf to make sure those accounts can only connect to the database from the defined application servers.<br />
<br />
create user app1_pool password 'app1_pool';<br />
grant app1_role to app1_pool;<br />
<br />
===Create Application Schemas===<br />
<br />
Create the application schema and grant the application users and roles the appropriate privileges. Make sure to create the application objects within the defined schema and set the appropriate object ownership.<br />
<br />
create schema app1 authorization app1;<br />
grant usage on schema app1 to app1_role;<br />
grant usage on schema app1 to app1_query_role;<br />
<br />
===Disclaimer===<br />
<br />
These are the recommendations from an experienced DBA. They can be followed or ignored. Personally by following these few simple methods of managing application schemas, it has made my life easier.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Database_Schema_Recommendations_for_an_Application&diff=18367Database Schema Recommendations for an Application2012-10-11T17:58:14Z<p>Rstephan: </p>
<hr />
<div>==Database Schema Recommendations for an Application==<br />
<br />
Within a PostgreSQL database cluster the basic methods for separating and name spacing objects is through [http://www.postgresql.org/docs/current/static/manage-ag-overview.html Managing Databases] and [http://www.postgresql.org/docs/current/static/ddl-schemas.html Schema Data Definitions].<br />
<br />
The recommendation is to create a single database with multiple named schemas. This is different than a common (and older) practice of creating multiple databases and storing the objects within the "public" schema. Additionally, it is recommended to remove the public schema.<br />
<br />
These are some of the advantages to following this recommendation:<br />
<br />
* Cross schema object access is possible from a single database connection.<br />
* Granting access to a schema is executed through a GRANT statement versus a reconfiguration of the pg_hba.conf file.<br />
* Schemas are the ANSI standard for object separation and name spacing.<br />
* Managing only one database within a single server (PostgreSQL cluster).<br />
<br />
An advantage to using the separate database method is sessions connected to a database cannot cross database boundaries. Separation of objects is more complete. However, if the desire is have more separation of the objects (i.e., different environments), it is recommended to create a new server.<br />
<br />
Note: Seperation at the databse level can be used for multi-tenant hosting when combined with the db_user_namespace configuration parameter. Schema seperation again becomes the important feature for seperating the database components within each of the hosted databases.<br />
<br />
===Creating Tablespaces===<br />
<br />
Create a set of tablespaces appropriate for each application. This provides for the physical separation of storage from one application to another. It also can provide for separation of storage within an application. Different storage of tables and indexes would enhance management flexibility and could provide additional performance.<br />
<br />
PostgreSQL expects the host operating system to provide device management. Create a file system for each tablespace to separate and control the tablespace storage. The following is an example using ZFS with storage pools named pgdatapool and pgindexpool. Two 10 GB tablespaces will be used for an application, one for tables and one for indexes.<br />
<br />
Unix commands:<br />
<br />
zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data<br />
zfs set refquota=10G pgdatapool/app1_data<br />
zfs set refreference=10G pgdatapool/app1_data<br />
zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index<br />
zfs set refquota=10G pgindexpool/app1_index<br />
zfs set refreference=10G pgindexpool/app1_index<br />
<br />
SQL commands:<br />
<br />
create tablespace app1_data location '/pgdata/app1_data';<br />
create tablespace app1_index location '/pgdata/app1_index';<br />
<br />
===Creating Accounts and Roles===<br />
<br />
PostgreSQL database objects have an account (role) ownership. Create an account synonymous with the schema that will name space the objects. This account will be used as the owner for the objects. For security purposes do not allow anyone to connect to the account owning the application database objects. This account will need access to the tablespaces previously created. When application objects are created make sure the ownership is properly set.<br />
<br />
create user app1 nologin;<br />
grant create on tablespace app1_data to app1;<br />
grant create on tablespace app1_index to app1;<br />
<br />
Grant database object privileges to users or application accounts through the use of roles. Create a set of roles appropriate for an application. For simplicity create at least two roles, one to use for read/write privileges for the application and one for query privileges. As database objects are created for the application perform the appropriate grants to the roles.<br />
<br />
create role app1_role;<br />
create role app1_query_role;<br />
<br />
Applications generally connect to the database using pooled (shared) accounts from application servers. Limit the knowledge of those accounts and their credentials. If possible, use definitions within the pg_hba.conf to make sure those accounts can only connect to the database from the defined application servers.<br />
<br />
create user app1_pool password 'app1_pool';<br />
grant app1_role to app1_pool;<br />
<br />
===Create Application Schemas===<br />
<br />
Create the application schema and grant the application users and roles the appropriate privileges. Make sure to create the application objects within the defined schema and set the appropriate object ownership.<br />
<br />
create schema app1 authorization app1;<br />
grant usage on schema app1 to app1_role;<br />
grant usage on schema app1 to app1_query_role;<br />
<br />
===Disclaimer===<br />
<br />
These are the recommendations from an experienced DBA. They can be followed or ignored. Personally by following these few simple methods of managing application schemas, it has made my life easier.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=File:Oracle-better-than-postgres.pdf&diff=18223File:Oracle-better-than-postgres.pdf2012-09-12T21:10:16Z<p>Rstephan: uploaded a new version of "File:Oracle-better-than-postgres.pdf"</p>
<hr />
<div></div>Rstephanhttps://wiki.postgresql.org/index.php?title=Database_Schema_Recommendations_for_an_Application&diff=17980Database Schema Recommendations for an Application2012-08-03T16:59:37Z<p>Rstephan: Undo revision 17977 by Rstephan (Talk)</p>
<hr />
<div>==Database Schema Recommendations for an Application==<br />
<br />
Within a PostgreSQL database cluster the basic methods for separating and name spacing objects is through [http://www.postgresql.org/docs/current/static/manage-ag-overview.html Managing Databases] and [http://www.postgresql.org/docs/current/static/ddl-schemas.html Schema Data Definitions].<br />
<br />
The recommendation is to create a single database with multiple named schemas. This is different than a common (and older) practice of creating multiple databases and storing the objects within the "public" schema. Additionally, it is recommended to remove the public schema.<br />
<br />
These are some of the advantages to following this recommendation:<br />
<br />
* Cross schema object access is possible from a single database connection.<br />
* Granting access to a schema is executed through a GRANT statement versus a reconfiguration of the pg_hba.conf file.<br />
* Schemas are the ANSI standard for object separation and name spacing.<br />
* Managing only one database within a single server (PostgreSQL cluster).<br />
<br />
An advantage to using the separate database method is sessions connected to a database cannot cross database boundaries. Separation of objects is more complete. However, if the desire is have more separation of the objects (i.e., different environments), it is recommended to create a new server.<br />
<br />
===Creating Tablespaces===<br />
<br />
Create a set of tablespaces appropriate for each application. This provides for the physical separation of storage from one application to another. It also can provide for separation of storage within an application. Different storage of tables and indexes would enhance management flexibility and could provide additional performance.<br />
<br />
PostgreSQL expects the host operating system to provide device management. Create a file system for each tablespace to separate and control the tablespace storage. The following is an example using ZFS with storage pools named pgdatapool and pgindexpool. Two 10 GB tablespaces will be used for an application, one for tables and one for indexes.<br />
<br />
Unix commands:<br />
<br />
zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data<br />
zfs set refquota=10G pgdatapool/app1_data<br />
zfs set refreference=10G pgdatapool/app1_data<br />
zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index<br />
zfs set refquota=10G pgindexpool/app1_index<br />
zfs set refreference=10G pgindexpool/app1_index<br />
<br />
SQL commands:<br />
<br />
create tablespace app1_data location '/pgdata/app1_data';<br />
create tablespace app1_index location '/pgdata/app1_index';<br />
<br />
===Creating Accounts and Roles===<br />
<br />
PostgreSQL database objects have an account (role) ownership. Create an account synonymous with the schema that will name space the objects. This account will be used as the owner for the objects. For security purposes do not allow anyone to connect to the account owning the application database objects. This account will need access to the tablespaces previously created. When application objects are created make sure the ownership is properly set.<br />
<br />
create user app1 nologin;<br />
grant create on tablespace app1_data to app1;<br />
grant create on tablespace app1_index to app1;<br />
<br />
Grant database object privileges to users or application accounts through the use of roles. Create a set of roles appropriate for an application. For simplicity create at least two roles, one to use for read/write privileges for the application and one for query privileges. As database objects are created for the application perform the appropriate grants to the roles.<br />
<br />
create role app1_role;<br />
create role app1_query_role;<br />
<br />
Applications generally connect to the database using pooled (shared) accounts from application servers. Limit the knowledge of those accounts and their credentials. If possible, use definitions within the pg_hba.conf to make sure those accounts can only connect to the database from the defined application servers.<br />
<br />
create user app1_pool password 'app1_pool';<br />
grant app1_role to app1_pool;<br />
<br />
===Create Application Schemas===<br />
<br />
Create the application schema and grant the application users and roles the appropriate privileges. Make sure to create the application objects within the defined schema and set the appropriate object ownership.<br />
<br />
create schema app1 authorization app1;<br />
grant usage on schema app1 to app1_role;<br />
grant usage on schema app1 to app1_query_role;<br />
<br />
===Disclaimer===<br />
<br />
These are the recommendations from an experienced DBA. They can be followed or ignored. Personally by following these few simple methods of managing application schemas, it has made my life easier.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Database_Schema_Recommendations_for_an_Application&diff=17979Database Schema Recommendations for an Application2012-08-03T16:58:37Z<p>Rstephan: Undo revision 17978 by Rstephan (Talk)</p>
<hr />
<div>==Database Schema Recommendations for an Application==<br />
<br />
Concur test 1<br />
<br />
Within a PostgreSQL database cluster the basic methods for separating and name spacing objects is through [http://www.postgresql.org/docs/current/static/manage-ag-overview.html Managing Databases] and [http://www.postgresql.org/docs/current/static/ddl-schemas.html Schema Data Definitions].<br />
<br />
The recommendation is to create a single database with multiple named schemas. This is different than a common (and older) practice of creating multiple databases and storing the objects within the "public" schema. Additionally, it is recommended to remove the public schema.<br />
<br />
These are some of the advantages to following this recommendation:<br />
<br />
* Cross schema object access is possible from a single database connection.<br />
* Granting access to a schema is executed through a GRANT statement versus a reconfiguration of the pg_hba.conf file.<br />
* Schemas are the ANSI standard for object separation and name spacing.<br />
* Managing only one database within a single server (PostgreSQL cluster).<br />
<br />
An advantage to using the separate database method is sessions connected to a database cannot cross database boundaries. Separation of objects is more complete. However, if the desire is have more separation of the objects (i.e., different environments), it is recommended to create a new server.<br />
<br />
===Creating Tablespaces===<br />
<br />
Create a set of tablespaces appropriate for each application. This provides for the physical separation of storage from one application to another. It also can provide for separation of storage within an application. Different storage of tables and indexes would enhance management flexibility and could provide additional performance.<br />
<br />
PostgreSQL expects the host operating system to provide device management. Create a file system for each tablespace to separate and control the tablespace storage. The following is an example using ZFS with storage pools named pgdatapool and pgindexpool. Two 10 GB tablespaces will be used for an application, one for tables and one for indexes.<br />
<br />
Unix commands:<br />
<br />
zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data<br />
zfs set refquota=10G pgdatapool/app1_data<br />
zfs set refreference=10G pgdatapool/app1_data<br />
zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index<br />
zfs set refquota=10G pgindexpool/app1_index<br />
zfs set refreference=10G pgindexpool/app1_index<br />
<br />
SQL commands:<br />
<br />
create tablespace app1_data location '/pgdata/app1_data';<br />
create tablespace app1_index location '/pgdata/app1_index';<br />
<br />
===Creating Accounts and Roles===<br />
<br />
PostgreSQL database objects have an account (role) ownership. Create an account synonymous with the schema that will name space the objects. This account will be used as the owner for the objects. For security purposes do not allow anyone to connect to the account owning the application database objects. This account will need access to the tablespaces previously created. When application objects are created make sure the ownership is properly set.<br />
<br />
create user app1 nologin;<br />
grant create on tablespace app1_data to app1;<br />
grant create on tablespace app1_index to app1;<br />
<br />
Grant database object privileges to users or application accounts through the use of roles. Create a set of roles appropriate for an application. For simplicity create at least two roles, one to use for read/write privileges for the application and one for query privileges. As database objects are created for the application perform the appropriate grants to the roles.<br />
<br />
create role app1_role;<br />
create role app1_query_role;<br />
<br />
Applications generally connect to the database using pooled (shared) accounts from application servers. Limit the knowledge of those accounts and their credentials. If possible, use definitions within the pg_hba.conf to make sure those accounts can only connect to the database from the defined application servers.<br />
<br />
create user app1_pool password 'app1_pool';<br />
grant app1_role to app1_pool;<br />
<br />
===Create Application Schemas===<br />
<br />
Create the application schema and grant the application users and roles the appropriate privileges. Make sure to create the application objects within the defined schema and set the appropriate object ownership.<br />
<br />
create schema app1 authorization app1;<br />
grant usage on schema app1 to app1_role;<br />
grant usage on schema app1 to app1_query_role;<br />
<br />
===Disclaimer===<br />
<br />
These are the recommendations from an experienced DBA. They can be followed or ignored. Personally by following these few simple methods of managing application schemas, it has made my life easier.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Database_Schema_Recommendations_for_an_Application&diff=17978Database Schema Recommendations for an Application2012-08-03T16:58:05Z<p>Rstephan: </p>
<hr />
<div>==Database Schema Recommendations for an Application==<br />
<br />
Concur test 2<br />
<br />
Within a PostgreSQL database cluster the basic methods for separating and name spacing objects is through [http://www.postgresql.org/docs/current/static/manage-ag-overview.html Managing Databases] and [http://www.postgresql.org/docs/current/static/ddl-schemas.html Schema Data Definitions].<br />
<br />
The recommendation is to create a single database with multiple named schemas. This is different than a common (and older) practice of creating multiple databases and storing the objects within the "public" schema. Additionally, it is recommended to remove the public schema.<br />
<br />
These are some of the advantages to following this recommendation:<br />
<br />
* Cross schema object access is possible from a single database connection.<br />
* Granting access to a schema is executed through a GRANT statement versus a reconfiguration of the pg_hba.conf file.<br />
* Schemas are the ANSI standard for object separation and name spacing.<br />
* Managing only one database within a single server (PostgreSQL cluster).<br />
<br />
An advantage to using the separate database method is sessions connected to a database cannot cross database boundaries. Separation of objects is more complete. However, if the desire is have more separation of the objects (i.e., different environments), it is recommended to create a new server.<br />
<br />
===Creating Tablespaces===<br />
<br />
Create a set of tablespaces appropriate for each application. This provides for the physical separation of storage from one application to another. It also can provide for separation of storage within an application. Different storage of tables and indexes would enhance management flexibility and could provide additional performance.<br />
<br />
PostgreSQL expects the host operating system to provide device management. Create a file system for each tablespace to separate and control the tablespace storage. The following is an example using ZFS with storage pools named pgdatapool and pgindexpool. Two 10 GB tablespaces will be used for an application, one for tables and one for indexes.<br />
<br />
Unix commands:<br />
<br />
zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data<br />
zfs set refquota=10G pgdatapool/app1_data<br />
zfs set refreference=10G pgdatapool/app1_data<br />
zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index<br />
zfs set refquota=10G pgindexpool/app1_index<br />
zfs set refreference=10G pgindexpool/app1_index<br />
<br />
SQL commands:<br />
<br />
create tablespace app1_data location '/pgdata/app1_data';<br />
create tablespace app1_index location '/pgdata/app1_index';<br />
<br />
===Creating Accounts and Roles===<br />
<br />
PostgreSQL database objects have an account (role) ownership. Create an account synonymous with the schema that will name space the objects. This account will be used as the owner for the objects. For security purposes do not allow anyone to connect to the account owning the application database objects. This account will need access to the tablespaces previously created. When application objects are created make sure the ownership is properly set.<br />
<br />
create user app1 nologin;<br />
grant create on tablespace app1_data to app1;<br />
grant create on tablespace app1_index to app1;<br />
<br />
Grant database object privileges to users or application accounts through the use of roles. Create a set of roles appropriate for an application. For simplicity create at least two roles, one to use for read/write privileges for the application and one for query privileges. As database objects are created for the application perform the appropriate grants to the roles.<br />
<br />
create role app1_role;<br />
create role app1_query_role;<br />
<br />
Applications generally connect to the database using pooled (shared) accounts from application servers. Limit the knowledge of those accounts and their credentials. If possible, use definitions within the pg_hba.conf to make sure those accounts can only connect to the database from the defined application servers.<br />
<br />
create user app1_pool password 'app1_pool';<br />
grant app1_role to app1_pool;<br />
<br />
===Create Application Schemas===<br />
<br />
Create the application schema and grant the application users and roles the appropriate privileges. Make sure to create the application objects within the defined schema and set the appropriate object ownership.<br />
<br />
create schema app1 authorization app1;<br />
grant usage on schema app1 to app1_role;<br />
grant usage on schema app1 to app1_query_role;<br />
<br />
===Disclaimer===<br />
<br />
These are the recommendations from an experienced DBA. They can be followed or ignored. Personally by following these few simple methods of managing application schemas, it has made my life easier.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Database_Schema_Recommendations_for_an_Application&diff=17977Database Schema Recommendations for an Application2012-08-03T16:57:59Z<p>Rstephan: </p>
<hr />
<div>==Database Schema Recommendations for an Application==<br />
<br />
Concur test 1<br />
<br />
Within a PostgreSQL database cluster the basic methods for separating and name spacing objects is through [http://www.postgresql.org/docs/current/static/manage-ag-overview.html Managing Databases] and [http://www.postgresql.org/docs/current/static/ddl-schemas.html Schema Data Definitions].<br />
<br />
The recommendation is to create a single database with multiple named schemas. This is different than a common (and older) practice of creating multiple databases and storing the objects within the "public" schema. Additionally, it is recommended to remove the public schema.<br />
<br />
These are some of the advantages to following this recommendation:<br />
<br />
* Cross schema object access is possible from a single database connection.<br />
* Granting access to a schema is executed through a GRANT statement versus a reconfiguration of the pg_hba.conf file.<br />
* Schemas are the ANSI standard for object separation and name spacing.<br />
* Managing only one database within a single server (PostgreSQL cluster).<br />
<br />
An advantage to using the separate database method is sessions connected to a database cannot cross database boundaries. Separation of objects is more complete. However, if the desire is have more separation of the objects (i.e., different environments), it is recommended to create a new server.<br />
<br />
===Creating Tablespaces===<br />
<br />
Create a set of tablespaces appropriate for each application. This provides for the physical separation of storage from one application to another. It also can provide for separation of storage within an application. Different storage of tables and indexes would enhance management flexibility and could provide additional performance.<br />
<br />
PostgreSQL expects the host operating system to provide device management. Create a file system for each tablespace to separate and control the tablespace storage. The following is an example using ZFS with storage pools named pgdatapool and pgindexpool. Two 10 GB tablespaces will be used for an application, one for tables and one for indexes.<br />
<br />
Unix commands:<br />
<br />
zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data<br />
zfs set refquota=10G pgdatapool/app1_data<br />
zfs set refreference=10G pgdatapool/app1_data<br />
zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index<br />
zfs set refquota=10G pgindexpool/app1_index<br />
zfs set refreference=10G pgindexpool/app1_index<br />
<br />
SQL commands:<br />
<br />
create tablespace app1_data location '/pgdata/app1_data';<br />
create tablespace app1_index location '/pgdata/app1_index';<br />
<br />
===Creating Accounts and Roles===<br />
<br />
PostgreSQL database objects have an account (role) ownership. Create an account synonymous with the schema that will name space the objects. This account will be used as the owner for the objects. For security purposes do not allow anyone to connect to the account owning the application database objects. This account will need access to the tablespaces previously created. When application objects are created make sure the ownership is properly set.<br />
<br />
create user app1 nologin;<br />
grant create on tablespace app1_data to app1;<br />
grant create on tablespace app1_index to app1;<br />
<br />
Grant database object privileges to users or application accounts through the use of roles. Create a set of roles appropriate for an application. For simplicity create at least two roles, one to use for read/write privileges for the application and one for query privileges. As database objects are created for the application perform the appropriate grants to the roles.<br />
<br />
create role app1_role;<br />
create role app1_query_role;<br />
<br />
Applications generally connect to the database using pooled (shared) accounts from application servers. Limit the knowledge of those accounts and their credentials. If possible, use definitions within the pg_hba.conf to make sure those accounts can only connect to the database from the defined application servers.<br />
<br />
create user app1_pool password 'app1_pool';<br />
grant app1_role to app1_pool;<br />
<br />
===Create Application Schemas===<br />
<br />
Create the application schema and grant the application users and roles the appropriate privileges. Make sure to create the application objects within the defined schema and set the appropriate object ownership.<br />
<br />
create schema app1 authorization app1;<br />
grant usage on schema app1 to app1_role;<br />
grant usage on schema app1 to app1_query_role;<br />
<br />
===Disclaimer===<br />
<br />
These are the recommendations from an experienced DBA. They can be followed or ignored. Personally by following these few simple methods of managing application schemas, it has made my life easier.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Database_Schema_Recommendations_for_an_Application&diff=17960Database Schema Recommendations for an Application2012-07-25T13:12:29Z<p>Rstephan: </p>
<hr />
<div>==Database Schema Recommendations for an Application==<br />
<br />
Within a PostgreSQL database cluster the basic methods for separating and name spacing objects is through [http://www.postgresql.org/docs/current/static/manage-ag-overview.html Managing Databases] and [http://www.postgresql.org/docs/current/static/ddl-schemas.html Schema Data Definitions].<br />
<br />
The recommendation is to create a single database with multiple named schemas. This is different than a common (and older) practice of creating multiple databases and storing the objects within the "public" schema. Additionally, it is recommended to remove the public schema.<br />
<br />
These are some of the advantages to following this recommendation:<br />
<br />
* Cross schema object access is possible from a single database connection.<br />
* Granting access to a schema is executed through a GRANT statement versus a reconfiguration of the pg_hba.conf file.<br />
* Schemas are the ANSI standard for object separation and name spacing.<br />
* Managing only one database within a single server (PostgreSQL cluster).<br />
<br />
An advantage to using the separate database method is sessions connected to a database cannot cross database boundaries. Separation of objects is more complete. However, if the desire is have more separation of the objects (i.e., different environments), it is recommended to create a new server.<br />
<br />
===Creating Tablespaces===<br />
<br />
Create a set of tablespaces appropriate for each application. This provides for the physical separation of storage from one application to another. It also can provide for separation of storage within an application. Different storage of tables and indexes would enhance management flexibility and could provide additional performance.<br />
<br />
PostgreSQL expects the host operating system to provide device management. Create a file system for each tablespace to separate and control the tablespace storage. The following is an example using ZFS with storage pools named pgdatapool and pgindexpool. Two 10 GB tablespaces will be used for an application, one for tables and one for indexes.<br />
<br />
Unix commands:<br />
<br />
zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data<br />
zfs set refquota=10G pgdatapool/app1_data<br />
zfs set refreference=10G pgdatapool/app1_data<br />
zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index<br />
zfs set refquota=10G pgindexpool/app1_index<br />
zfs set refreference=10G pgindexpool/app1_index<br />
<br />
SQL commands:<br />
<br />
create tablespace app1_data location '/pgdata/app1_data';<br />
create tablespace app1_index location '/pgdata/app1_index';<br />
<br />
===Creating Accounts and Roles===<br />
<br />
PostgreSQL database objects have an account (role) ownership. Create an account synonymous with the schema that will name space the objects. This account will be used as the owner for the objects. For security purposes do not allow anyone to connect to the account owning the application database objects. This account will need access to the tablespaces previously created. When application objects are created make sure the ownership is properly set.<br />
<br />
create user app1 nologin;<br />
grant create on tablespace app1_data to app1;<br />
grant create on tablespace app1_index to app1;<br />
<br />
Grant database object privileges to users or application accounts through the use of roles. Create a set of roles appropriate for an application. For simplicity create at least two roles, one to use for read/write privileges for the application and one for query privileges. As database objects are created for the application perform the appropriate grants to the roles.<br />
<br />
create role app1_role;<br />
create role app1_query_role;<br />
<br />
Applications generally connect to the database using pooled (shared) accounts from application servers. Limit the knowledge of those accounts and their credentials. If possible, use definitions within the pg_hba.conf to make sure those accounts can only connect to the database from the defined application servers.<br />
<br />
create user app1_pool password 'app1_pool';<br />
grant app1_role to app1_pool;<br />
<br />
===Create Application Schemas===<br />
<br />
Create the application schema and grant the application users and roles the appropriate privileges. Make sure to create the application objects within the defined schema and set the appropriate object ownership.<br />
<br />
create schema app1 authorization app1;<br />
grant usage on schema app1 to app1_role;<br />
grant usage on schema app1 to app1_query_role;<br />
<br />
===Disclaimer===<br />
<br />
These are the recommendations from an experienced DBA. They can be followed or ignored. Personally by following these few simple methods of managing application schemas, it has made my life easier.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Database_Schema_Recommendations_for_an_Application&diff=17959Database Schema Recommendations for an Application2012-07-25T13:11:04Z<p>Rstephan: /* Database Schema Recommendations for an Application */</p>
<hr />
<div>==Database Schema Recommendations for an Application==<br />
<br />
Within a PostgreSQL database cluster the basic methods for separating and name spacing objects is through [http://www.postgresql.org/docs/current/static/manage-ag-overview.html Managing Databases] and [http://www.postgresql.org/docs/current/static/ddl-schemas.html Schema Data Definitions].<br />
<br />
The recommendation is to create a single database with multiple named schemas. This is different than a common (and older) practice of creating multiple databases and storing the objects within the "public" schema. Additionally, it is recommended to remove the public schema.<br />
<br />
These are some of the advantages to following this recommendation:<br />
<br />
* Cross schema object access is possible from a single database connection.<br />
* Granting access to a schema is executed through a GRANT statement versus a reconfiguration of the pg_hba.conf file.<br />
* Schemas are the ANSI standard for object separation and name spacing.<br />
* Managing only one database within a single server (PostgreSQL cluster).<br />
<br />
An advantage to using the separate database method is sessions connected to a database cannot cross database boundaries. Separation of objects is more complete. However, if the desire is have more separation of the objects (i.e., different environments), it is recommended to create a new server.<br />
<br />
===Creating Tablespaces===<br />
<br />
Create a set of tablespaces appropriate for each application. This provides for the physical separation of storage from one application to another. It also can provide for separation of storage within an application. Different storage of tables and indexes would enhance management flexibility and could provide additional performance.<br />
<br />
PostgreSQL expects the host operating system to provide device management. Create a file system for each tablespace to separate and control the tablespace storage. The following is an example using ZFS with storage pools named pgdatapool and pgindexpool. Two 10 GB tablespaces will be used for an application, one for tables and one for indexes.<br />
<br />
Unix commands:<br />
<br />
zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data<br />
zfs set refquota=10G pgdatapool/app1_data<br />
zfs set refreference=10G pgdatapool/app1_data<br />
zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index<br />
zfs set refquota=10G pgindexpool/app1_index<br />
zfs set refreference=10G pgindexpool/app1_index<br />
<br />
SQL commands:<br />
<br />
create tablespace app1_data location '/pgdata/app1_data';<br />
create tablespace app1_index location '/pgdata/app1_index';<br />
<br />
===Creating Accounts and Roles===<br />
<br />
PostgreSQL database objects have an account (role) ownership. Create an account synonymous with the schema that will name space the objects. This account will be used as the owner for the objects. For security purposes do not allow anyone to connect to the account owning the application database objects. This account will need access to the tablespaces previously created. When application objects are created make sure the ownership is properly set.<br />
<br />
create user app1 nologin;<br />
grant create on tablespace app1_data to app1;<br />
grant create on tablespace app1_index to app1;<br />
<br />
Grant database object privileges to users or application accounts through the use of roles. Create a set of roles appropriate for an application. For simplicity create at least two roles, one to use for read/write privileges for the application and one for query privileges. As database objects are created for the application perform the appropriate grants to the roles.<br />
<br />
create role app1_role;<br />
create role app1_query_role;<br />
<br />
Applications generally connect to the database using pooled (shared) accounts from application servers. Limit the knowledge of those accounts and their credentials. If possible, use definitions within the pg_hba.conf to make sure those accounts can only connect to the database from the defined application servers.<br />
<br />
create user app1_pool password 'app1_pool';<br />
grant app1_role to app1_pool;<br />
<br />
===Create Application Schemas===<br />
<br />
Create the application schema and grant the application users and roles the appropriate privileges. Make sure to create the application objects within the defined schema and set the appropriate object ownership.<br />
<br />
create schema app1 authorization app1;<br />
grant usage on schema app1 to app1_role;<br />
grant usage on schema app1 to app1_query_role;<br />
<br />
===Disclaimer===<br />
<br />
These are the recommendations from an experienced DBA. They can be followed or ignored. Personally by following these few simple methods of managing application schemas, it has made my life easier.</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Database_Schema_Recommendations_for_an_Application&diff=17958Database Schema Recommendations for an Application2012-07-25T13:00:57Z<p>Rstephan: Created page with "==Database Schema Recommendations for an Application== Within a PostgreSQL database cluster the basic methods for separating and name spacing objects is through [http://www.post…"</p>
<hr />
<div>==Database Schema Recommendations for an Application==<br />
<br />
Within a PostgreSQL database cluster the basic methods for separating and name spacing objects is through [http://www.postgresql.org/docs/current/static/manage-ag-overview.html Managing Databases] and [http://www.postgresql.org/docs/current/static/ddl-schemas.html Schema Data Definitions].<br />
<br />
The recommendation is to create a single database with multiple named schemas. This is different than a common (and older) practice of creating multiple databases and storing the objects within the "public" schema. Additionally, it is recommended to remove the public schema.<br />
<br />
These are some of the advantages to following this recommendation:<br />
<br />
* Cross schema object access is possible from a single database connection.<br />
* Granting access to a schema is executed through a GRANT statement versus a reconfiguration of the pg_hba.conf file.<br />
* Schemas are the ANSI standard for object separation and name spacing.<br />
* Managing only one database within a single server (PostgreSQL cluster).<br />
<br />
An advantage to using the separate database method is sessions connected to a database cannot cross database boundaries. Separation of objects is more complete. However, if the desire is have more separation of the objects (i.e., different environments), it is recommended to create a new server.<br />
<br />
===Creating Tablespaces===<br />
<br />
Create a set of tablespaces appropriate for each application. This provides for the physical separation of storage from one application to another. It also can provide for separation of storage within an application. Different storage of tables and indexes would enhance management flexibility and could provide additional performance.<br />
<br />
PostgreSQL expects the host operating system to provide device management. Create a file system for each tablespace to separate and control the tablespace storage. The following is an example using ZFS with storage pools named pgdatapool and pgindexpool. Two 10 GB tablespaces will be used for an application, one for tables and one for indexes.<br />
<br />
Unix commands:<br />
<br />
zfs create -o mountpoint=/pgdata/app1_data pgdatapool/app1_data<br />
zfs set refquota=10G pgdatapool/app1_data<br />
zfs set refreference=10G pgdatapool/app1_data<br />
zfs create -o mountpoint=/pgdata/app1_index pgindexpool/app1_index<br />
zfs set refquota=10G pgindexpool/app1_index<br />
zfs set refreference=10G pgindexpool/app1_index<br />
<br />
SQL commands:<br />
<br />
create tablespace app1_data location '/pgdata/app1_data';<br />
create tablespace app1_index location '/pgdata/app1_index';<br />
<br />
===Creating Accounts and Roles===<br />
<br />
PostgreSQL database objects have an account (role) ownership. Create an account synonymous with the schema that will name space the objects. This account will be used as the owner for the objects. For security purposes do not allow anyone to connect to the account owning the application database objects. This account will need access to the tablespaces previously created. When application objects are created make sure the ownership is properly set.<br />
<br />
create user app1 nologin;<br />
grant create on tablespace app1_data to app1;<br />
grant create on tablespace app1_index to app1;<br />
<br />
Grant database object privileges to users or application accounts through the use of roles. Create a set of roles appropriate for an application. For simplicity create at least two roles, one to use for read/write privileges for the application and one for query privileges. As database objects are created for the application perform the appropriate grants to the roles.<br />
<br />
create role app1_role;<br />
create role app1_query_role;<br />
<br />
Applications generally connect to the database using pooled (shared) accounts from application servers. Limit the knowledge of those accounts and their credentials. If possible, use definitions within the pg_hba.conf to make sure those accounts can only connect to the database from the defined application servers.<br />
<br />
create user app1_pool password 'app1_pool';<br />
grant app1_role to app1_pool;<br />
<br />
===Create Application Schemas===<br />
<br />
Create the application schema and grant the application users and roles the appropriate privileges. Make sure to create the application objects within the defined schema and set the appropriate object ownership.<br />
<br />
create schema app1 authorization app1;<br />
grant usage on schema app1 to app1_role;<br />
grant usage on schema app1 to app1_query_role;</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Category:Administration&diff=17957Category:Administration2012-07-25T12:59:07Z<p>Rstephan: /* General Admin Topics */</p>
<hr />
<div>== General Admin Topics ==<br />
<br />
*[[Client Authentication]] (pg_hba.conf)<br />
*[[Binary Replication Tutorial]]<br />
*[[Planner Statistics]]<br />
*[[Warm Standby]]<br />
* [[Replication, Clustering, and Connection Pooling]]<br />
* [[Shared Database Hosting]]<br />
* [http://www.ibm.com/developerworks/opensource/library/os-postgresecurity/index.html Total security in a PostgreSQL database] 2009-11-17<br />
* [[Simple Configuration Recommendation]]<br />
* [[Database Schema Recommendations for an Application]]<br />
<br />
== [[:Category:Backup|Backup]] ==<br />
<br />
See the [[:Category:Backup|Backup]] category.<br />
<br />
[http://www.postgresonline.com/journal/archives/186-postgresql90_pg_dumprestore.html Backup and Restore cheatsheet for PostgreSQL 9.0] Postgres OnLine Journal 2010-11-21<br />
<br />
== Authentication ==<br />
* [[Client Authentication]]<br />
<br />
== Restoration and Recovery ==<br />
* [http://svana.org/kleptog/pgsql/pgfsck.html PostgreSQL table checker and dumper tool] by Martijn van Oosterhout<br />
* [[Adventures in PostgreSQL, Episode 1]] by Josh Berkus (2002-05)<br />
<br />
== Routine maintenance and monitoring ==<br />
*[[Vacuuming]]<br />
*[[Monitoring]]<br />
*[[Lock Monitoring]]<br />
*[[Index Maintenance]]<br />
*[[Disk Usage]]<br />
<br />
== [[:Category:Windows|Windows-specific]] ==<br />
<br />
[[:Category:Windows|Windows category]]<br />
[[Category:General articles and guides]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17774Simple Configuration Recommendation2012-06-08T12:15:58Z<p>Rstephan: /* Software Location and Ownership */</p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
These recommendations are to standardize and simplify PostgreSQL database configurations.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is easier to manage.<br />
<br />
The owner and group of the software, database files, and server processes should be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. An alternate and more manageable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones and FreeBSD jails or hypervisors like [http://en.wikipedia.org/wiki/Xen Xen] and [http://en.wikipedia.org/wiki/Kernel-based_Virtual_Machine KVM] make creation of multiple clusters within a single host unnecessary. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. [http://en.wikipedia.org/wiki/ZFS ZFS] is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/current/static/continuous-archiving.html Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual. <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
File system delegated administration is an advantage for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17773Simple Configuration Recommendation2012-06-08T12:13:44Z<p>Rstephan: /* Physical Database Backups */</p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
These recommendations are to standardize and simplify PostgreSQL database configurations.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is easier to manage.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. An alternate and more manageable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones and FreeBSD jails or hypervisors like [http://en.wikipedia.org/wiki/Xen Xen] and [http://en.wikipedia.org/wiki/Kernel-based_Virtual_Machine KVM] make creation of multiple clusters within a single host unnecessary. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. [http://en.wikipedia.org/wiki/ZFS ZFS] is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/current/static/continuous-archiving.html Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual. <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
File system delegated administration is an advantage for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17716Simple Configuration Recommendation2012-06-06T16:24:26Z<p>Rstephan: /* Account Management */</p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
These recommendations are to standardize and simplify PostgreSQL database configurations.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is easier to manage.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. An alternate and more manageable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones and FreeBSD jails or hypervisors like [http://en.wikipedia.org/wiki/Xen Xen] and [http://en.wikipedia.org/wiki/Kernel-based_Virtual_Machine KVM] make creation of multiple clusters within a single host unnecessary. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. [http://en.wikipedia.org/wiki/ZFS ZFS] is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/current/static/continuous-archiving.html Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual. <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
Delegation of file system administration is once again a necessity for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=User:Rstephan&diff=17706User:Rstephan2012-06-01T18:26:13Z<p>Rstephan: </p>
<hr />
<div>My name is Richard Stephan. I currently work at the [http://www.nyiso.com NYISO], and started using PostgreSQL 8.0 in 2005 for my own needs. In January of 2009, I finally convinced management to use PostgreSQL instead of Oracle for one of the applications. It was successful, and others have been created since.<br />
<br />
As an advocate for PostgreSQL, I started a local users group in the Capital District of New York State - [[NYCDPUG]].</div>Rstephanhttps://wiki.postgresql.org/index.php?title=User:Rstephan&diff=17705User:Rstephan2012-06-01T18:26:01Z<p>Rstephan: </p>
<hr />
<div>My name is Richard Stephan. I currently work at the [http://www.nyiso.com NYISO], and started using PostgreSQL 8.0 in 2005 for my own needs. In January of 2009, I finally convinced management to use PostgreSQL instead of Oracle for one of the applications. It was successful, and others have been created since.<br />
<br />
As an advocate for PostgreSQL, I started a local users group in the Capital District of New York State - [NYCDPUG].</div>Rstephanhttps://wiki.postgresql.org/index.php?title=User:Rstephan&diff=17704User:Rstephan2012-06-01T18:23:37Z<p>Rstephan: </p>
<hr />
<div>My name is Richard Stephan. I currently work at the [http://www.nyiso.com NYISO], and started using PostgreSQL 8.0 in 2005 for my own needs. In January of 2009, I finally convinced management to use PostgreSQL instead of Oracle for one of the applications. It was successful, and others have been created since.<br />
<br />
As an advocate for PostgreSQL, I started a local users group in the Capital District of New York State - NYCDPUG. Unfortunately, the group no longer meets.</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17703Simple Configuration Recommendation2012-06-01T18:18:59Z<p>Rstephan: /* Single Cluster and Database per Server */</p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
These recommendations are to standardize and simplify PostgreSQL database configurations.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is easier to manage.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. An alternate and more manageable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones and FreeBSD jails or hypervisors like [http://en.wikipedia.org/wiki/Xen Xen] and [http://en.wikipedia.org/wiki/Kernel-based_Virtual_Machine KVM] make creation of multiple clusters within a single host unnecessary. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. [http://en.wikipedia.org/wiki/ZFS ZFS] is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still <br />
<br />
use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/current/static/continuous-archiving.html Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual. <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
Delegation of file system administration is once again a necessity for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17702Simple Configuration Recommendation2012-06-01T18:12:57Z<p>Rstephan: /* File System Layouts */</p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
These recommendations are to standardize and simplify PostgreSQL database configurations.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is easier to manage.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. An alternate and more manageable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones or hypervisors like Xen or KVM make creation of multiple clusters within a single host unnecessary. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. [http://en.wikipedia.org/wiki/ZFS ZFS] is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still <br />
<br />
use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/current/static/continuous-archiving.html Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual. <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
Delegation of file system administration is once again a necessity for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17701Simple Configuration Recommendation2012-06-01T18:09:49Z<p>Rstephan: /* File System Layouts */</p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
These recommendations are to standardize and simplify PostgreSQL database configurations.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is easier to manage.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. An alternate and more manageable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones or hypervisors like Xen or KVM make creation of multiple clusters within a single host unnecessary. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. ZFS is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still <br />
<br />
use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/current/static/continuous-archiving.html Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual. <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
Delegation of file system administration is once again a necessity for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17693Simple Configuration Recommendation2012-05-31T12:00:35Z<p>Rstephan: /* Physical Database Backups */</p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
These recommendations are to standardize and simplify PostgreSQL database configurations.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is easier to manage.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. An alternate and more manageable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones or hypervisors like Xen or KVM make creation of multiple clusters within a single host unnecessary. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For <br />
<br />
physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system <br />
<br />
snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. ZFS is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still <br />
<br />
use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/current/static/continuous-archiving.html Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual. <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
Delegation of file system administration is once again a necessity for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17692Simple Configuration Recommendation2012-05-31T11:54:43Z<p>Rstephan: </p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
These recommendations are to standardize and simplify PostgreSQL database configurations.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is easier to manage.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. An alternate and more manageable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones or hypervisors like Xen or KVM make creation of multiple clusters within a single host unnecessary. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For <br />
<br />
physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system <br />
<br />
snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. ZFS is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still <br />
<br />
use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/9.1/static/continuous-archiving.html Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual. <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
Delegation of file system administration is once again a necessity for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17675Simple Configuration Recommendation2012-05-30T19:48:41Z<p>Rstephan: /* Physical Database Backups */</p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is preferable.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. The preferable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones or hypervisors like Xen or KVM make creation of multiple clusters within a single host unnecessary and undesirable. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For <br />
<br />
physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system <br />
<br />
snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. ZFS is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still <br />
<br />
use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/9.1/static/continuous-archiving.html Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual. <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
Delegation of file system administration is once again a necessity for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17674Simple Configuration Recommendation2012-05-30T19:46:12Z<p>Rstephan: /* Physical Database Backups */</p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is preferable.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. The preferable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones or hypervisors like Xen or KVM make creation of multiple clusters within a single host unnecessary and undesirable. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For <br />
<br />
physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system <br />
<br />
snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. ZFS is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still <br />
<br />
use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the ''Continuous Archiving and Point-In-Time Recovery'' chapter in the PostgreSQL reference manual - http://www.postgresql.org/docs/9.1/static/continuous-archiving.html <br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
Delegation of file system administration is once again a necessity for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17673Simple Configuration Recommendation2012-05-30T19:38:20Z<p>Rstephan: /* Physical Database Backups */</p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is preferable.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. The preferable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones or hypervisors like Xen or KVM make creation of multiple clusters within a single host unnecessary and undesirable. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For <br />
<br />
physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system <br />
<br />
snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. ZFS is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still <br />
<br />
use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the [http://www.postgresql.org/docs/9.1/static/continuous-archiving.html|Continuous Archiving and Point-In-Time Recovery] chapter in the PostgreSQL reference manual.<br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
Delegation of file system administration is once again a necessity for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17672Simple Configuration Recommendation2012-05-30T19:30:23Z<p>Rstephan: </p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is preferable.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. The preferable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones or hypervisors like Xen or KVM make creation of multiple clusters within a single host unnecessary and undesirable. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For <br />
<br />
physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system <br />
<br />
snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. ZFS is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still <br />
<br />
use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the Continuous Archiving and Point-In-Time <br />
<br />
Recovery chapter in the PostgreSQL reference manual.<br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
Delegation of file system administration is once again a necessity for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.<br />
<br />
[[Category:Administration]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Simple_Configuration_Recommendation&diff=17671Simple Configuration Recommendation2012-05-30T19:26:01Z<p>Rstephan: Created page with "==PostgreSQL Configuration Recommendations== Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closel…"</p>
<hr />
<div>==PostgreSQL Configuration Recommendations==<br />
Administration of database environments requires resources from separate disciplines. Database administrators (DBA) must work closely with the system and storage administrators. PostgreSQL relies heavily on the host operating system (OS) for storage management. It does not have the advanced and complicated features of Oracle for storage management.<br />
<br />
===Software Location and Ownership===<br />
The common location for PostgreSQL software on Linux is /usr/local/pgsql with the executables, source, and data existing in various subdirectories. However, PostgreSQL is open source software and whoever the distributor, packager, or supporter will have their <br />
recommendations as to where to place the software and what account owns the software.<br />
<br />
Some package management software place the executables, libraries, man pages, and contrib files in various places. Avoid these solutions. Having a standard simple configuration for the software installation is preferable.<br />
<br />
The owner and group of the software, database files, and server processes will be postgres:dba. The UID and GID have to be worked out with system administration.<br />
<br />
Create a base software destination directory:<br />
/opt/postgres<br />
<br />
Define the software installation using the first 2 digits of the software version (9.0 as the example): <br />
/opt/postgres/9.0<br />
<br />
Be advised upgrading with the third digit in the version number usually entails stopping the server, switching to the new software, and restarting the server. However, upgrading the first or second digit requires an upgrade of all of the data files. Keeping the software versions separate helps with upgrades.<br />
<br />
===Single Cluster and Database per Server===<br />
The following database objects are cluster wide within PostgreSQL, having only one database per cluster is preferable:<br />
* Configuration files<br />
* WAL (on-line and archived) files<br />
* Tablespaces<br />
* User accounts and roles<br />
* Server log file<br />
<br />
An older style of database object separation was through the use of multiple databases. The preferable method to separate database objects within a single database server is through the use of schemas.<br />
<br />
To separate PostgreSQL clusters within a server different data areas and IP port numbers need to be created. However, the virtualization capabilities of the OSes like Solaris’s zones or hypervisors like Xen or KVM make creation of multiple clusters within a single host unnecessary and undesirable. The recommendation is to have only one PostgreSQL cluster per virtualized host.<br />
<br />
===File System Layouts===<br />
To create the most flexible and manageable environment, separate the various database components into their own file systems. Create the following file systems (mount points):<br />
<br />
{| border="1"<br />
|/pgarchive<br />
|DB Archive location containing the archive log files.<br />
|-<br />
|/pgbackup<br />
|DB Backup location containing the physical and logical backups. For logical backups (pg_dump), use EXPORTS as a sub directory. For <br />
<br />
physical backups, use FULL{date}, convention for the sub directory. However, physical backups could be handled with file system <br />
<br />
snapshots. More on this later.<br />
|-<br />
|/pgcluster/data<br />
|PostgreSQL Cluster data directory (PGDATA environment variable). This will contain all of the configuration files and directories of a PostgreSQL cluster. Note: the on-line WAL files would be located in /pgcluster/data/pg_xlog.<br />
|-<br />
|/pgcluster/log<br />
|The location of the server log files.<br />
|-<br />
|/pgdata/system<br />
|The location of a database’s default tablespace. This is to be used at the creation of the database. The database catalog information will be stored.<br />
|-<br />
|/pgdata/temp<br />
|The location of the database’s default temporary tablespace. This is needed for temporary sort information. Note: The pgsql_tmp directory within the default tablespace will be used if a temporary tablespace is not defined. <br />
|-<br />
|/pgdata/''app_tblspc''<br />
|Application tablespaces. For every schema there should be a minimum of two tablespaces. One for tables and one for indexes.<br />
|}<br />
<br />
PostgreSQL does not have declarative size limitations for its tablespaces and database objects; the OS is expected to manage the size of used devices. This is why it is recommended to create a separate mount point (file system) for every tablespace. This adds a layer of complexity especially in organizations that segregate storage and OS management from database management. However, that level of complexity is outweighed by the advantage of separation and segregation of database objects.<br />
<br />
It is desirable for the file system growth and management to be in the form of distributed administration. A DBA would be given a set of disk groups within a volume manager and then carve up the file systems accordingly. ZFS is an example of a file system that has delegated administration.<br />
<br />
===Server Configuration Information===<br />
Use "Continuous Archiving" for Point-In-Time Recovery (PITR).<br />
archive_mode = on<br />
archive_command = 'cp %p /pgarchive/%f'<br />
wal_level = 'archive'<br />
<br />
Setup a server log file rotation. (7 days or 10MB, whichever comes first)<br />
log_directory = '/pgcluster/log'<br />
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'<br />
log_rotation_age = 7d<br />
log_rotation_size = 10MB<br />
log_truncate_on_rotation = off<br />
log_line_prefix = '%t c% '<br />
<br />
Gather connection information in server log file.<br />
log_connections = on<br />
log_disconnections = on<br />
<br />
Log DDL transactions.<br />
log_statement = 'ddl'<br />
<br />
Enable SSL traffic.<br />
ssl = on<br />
ssl_ciphers = 'ALL'<br />
<br />
Either drop the default postgres database or deny remote connections to it.<br />
<br />
Create a database to place application schemas within. Drop the public schema.<br />
<br />
===Account Management===<br />
Avoid connecting to the database server as the database superuser, postgres. Management processes, like backups, will most likely still <br />
<br />
use the postgres account; however users and applications should not. Allow only local connections to the postgres database user. Note: In version 9.1 using the authentication model within pg_hba.conf of local with auth-option peer is the most preferable. <br />
<br />
Create individual accounts for all the users that will be connecting directly to the database. DBAs will need superuser privileges, deployment representatives will need privileges to manipulate schema object definitions, developers will need select privileges on application objects to diagnose production issues. <br />
<br />
Where possible use centralized enterprise accounts (i.e., LDAP) for user account authentication.<br />
<br />
Create accounts to be synonymous with application schemas. Avoid connecting to those schema accounts. In fact where possible make the account NOLOGIN.<br />
<br />
When users are deploying object definitions into the application schemas, they will need to have the appropriate privileges. Granting those users the role of the application schema is sufficient to allow this activity. Make sure that for any newly created object the ownership is set to the account that matches the schema.<br />
<br />
To ease management of accounts, use roles for granting privileges to users versus direct grants.<br />
<br />
Generally applications connect to the database using pooled (shared) accounts. Make sure those accounts can only connect to the database from the defined application servers. Users should not be allowed to log directly into the database using those pooled accounts.<br />
<br />
===Physical Database Backups===<br />
To perform on-line backups it is important that the database be in archive log mode. Refer to the Continuous Archiving and Point-In-Time <br />
<br />
Recovery chapter in the PostgreSQL reference manual.<br />
<br />
Using an advanced file system like ZFS that has snapshot/rollback capabilities has some significant advantages. Placing the database in hot backup mode, snapshoting the file systems that make up the database storage, and taking the database out of backup mode is preferable to using tar or cpio to copy all of the data files to an alternate location during the backup process. <br />
<br />
After the snapshots have been taken coping the data files to an alternate location for safe keeping is still an option; however, the database is only in hot backup mode for a short amount of time while the snapshot is taken. For most recovery situations using the on-line backups (the snapshots) is used instead of "pulling from tape".<br />
<br />
Delegation of file system administration is once again a necessity for management of the file system snapshots. DBAs will have to coordinate with the system and storage administration to facilitate the best practices.</div>Rstephanhttps://wiki.postgresql.org/index.php?title=Category:Administration&diff=17670Category:Administration2012-05-30T19:05:53Z<p>Rstephan: /* General Admin Topics */</p>
<hr />
<div>== General Admin Topics ==<br />
<br />
*[[Client Authentication]] (pg_hba.conf)<br />
*[[Binary Replication Tutorial]]<br />
*[[Planner Statistics]]<br />
*[[Warm Standby]]<br />
* [[Replication, Clustering, and Connection Pooling]]<br />
* [[Shared Database Hosting]]<br />
* [http://www.ibm.com/developerworks/opensource/library/os-postgresecurity/index.html Total security in a PostgreSQL database] 2009-11-17<br />
* [[Simple Configuration Recommendation]]<br />
<br />
== [[:Category:Backup|Backup]] ==<br />
<br />
See the [[:Category:Backup|Backup]] category.<br />
<br />
[http://www.postgresonline.com/journal/archives/186-postgresql90_pg_dumprestore.html Backup and Restore cheatsheet for PostgreSQL 9.0] Postgres OnLine Journal 2010-11-21<br />
<br />
== Authentication ==<br />
* [[Client Authentication]]<br />
<br />
== Restoration and Recovery ==<br />
* [http://svana.org/kleptog/pgsql/pgfsck.html PostgreSQL table checker and dumper tool] by Martijn van Oosterhout<br />
* [[Adventures in PostgreSQL, Episode 1]] by Josh Berkus (2002-05)<br />
<br />
== Routine maintenance and monitoring ==<br />
*[[Vacuuming]]<br />
*[[Monitoring]]<br />
*[[Lock Monitoring]]<br />
*[[Index Maintenance]]<br />
*[[Disk Usage]]<br />
<br />
== [[:Category:Windows|Windows-specific]] ==<br />
<br />
[[:Category:Windows|Windows category]]<br />
[[Category:General articles and guides]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=17651NYCDPUG2012-05-24T20:06:40Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group no longer exists. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [http://wiki.postgresql.org/images/4/46/NYCDPUG_NYISO_PostgreSQL.pdf NYCDPUG_NYISO_PostrgreSQL.pdf]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]<br />
* May 6, 2010 - Separation of Duties [http://wiki.postgresql.org/images/1/1c/SeparationofDuties.pdf NYISO_Separation_of_Duties.pdf]<br />
* March 3, 2011 - Securing data in transit using SSL and SSL/TLS LDAP.<br />
* May 5, 2011 - Jim Mlodgenski's Scaling PostgreSQL with Stado<br />
* November 3, 2011 - Why is Oracle Better than PostgreSQL? [http://wiki.postgresql.org/images/2/27/Oracle-better-than-postgres.pdf Oracle-better-than-postgres.pdf]<br />
<br />
[[Category:Users group]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=17650NYCDPUG2012-05-24T20:04:18Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group no longer exists. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [http://wiki.postgresql.org/images/4/46/NYCDPUG_NYISO_PostgreSQL.pdf NYCDPUG_NYISO_PostrgreSQL.pdf]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]<br />
* May 6, 2010 - Separation of Duties [http://wiki.postgresql.org/images/1/1c/SeparationofDuties.pdf NYISO_Separation_of_Duties.pdf]<br />
* March 3, 2011 - Securing data in transit using SSL and SSL/TLS LDAP.<br />
* May 5, 2011 - Jim Mlodgenski's Scaling PostgreSQL with Stado<br />
* November 3, 2011 - Why is Oracle Better than PostgreSQL? [http://wiki.postgresql.org/images/2/27/Oracle-better-than-postgres.pdf Oracle-better-than-postgres.pdf <br />
]<br />
<br />
[[Category:Users group]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=File:Oracle-better-than-postgres.pdf&diff=17649File:Oracle-better-than-postgres.pdf2012-05-24T20:00:43Z<p>Rstephan: </p>
<hr />
<div></div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=17648NYCDPUG2012-05-24T19:58:58Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group no longer exists. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [http://wiki.postgresql.org/images/4/46/NYCDPUG_NYISO_PostgreSQL.pdf NYCDPUG_NYISO_PostrgreSQL.pdf]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]<br />
* May 6, 2010 - Separation of Duties [http://wiki.postgresql.org/images/1/1c/SeparationofDuties.pdf NYISO_Separation_of_Duties.pdf]<br />
* March 3, 2011 - Securing data in transit using SSL and SSL/TLS LDAP.<br />
* May 5, 2011 - Jim Mlodgenski's Scaling PostgreSQL with Stado<br />
* November 3, 2011 - Why is Oracle Better than PostgreSQL?<br />
<br />
[[Category:Users group]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=17647NYCDPUG2012-05-24T19:58:41Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group no longer exists. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [http://wiki.postgresql.org/images/4/46/NYCDPUG_NYISO_PostgreSQL.pdf NYCDPUG_NYISO_PostrgreSQL.pdf]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]<br />
* May 6, 2010 - Separation of Duties [http://wiki.postgresql.org/images/1/1c/SeparationofDuties.pdf NYISO_Separation_of_Duties.pdf]<br />
* March 3, 2011 - Securing data in transit using SSL and SSL/TLS LDAP.<br />
* May 5, 2011 - Jim Mlodgenski's Scaling PostgreSQL with Stado<br />
* November 3, 2011 - Why is Oracle Better than PostgreSQL<br />
<br />
[[Category:Users group]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=15082NYCDPUG2011-08-05T18:49:22Z<p>Rstephan: Undo revision 15081 by Rstephan (Talk)</p>
<hr />
<div>The New York Capital District PostgreSQL Users Group home page can be found at [http://nycdpug.x10hosting.com http://nycdpug.x10hosting.com]. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [http://wiki.postgresql.org/images/4/46/NYCDPUG_NYISO_PostgreSQL.pdf NYCDPUG_NYISO_PostrgreSQL.pdf]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]<br />
* May 6, 2010 - Separation of Duties [http://wiki.postgresql.org/images/1/1c/SeparationofDuties.pdf NYISO_Separation_of_Duties.pdf]<br />
* March 3, 2011 - Securing data in transit using SSL and SSL/TLS LDAP.<br />
* May 5, 2011 - Jim Mlodgenski's Scaling PostgreSQL with Stado</div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=15081NYCDPUG2011-08-05T18:48:24Z<p>Rstephan: Undo revision 15080 by Rstephan (Talk)</p>
<hr />
<div>The New York Capital District PostgreSQL Users Group home page can be found at [http://nycdpug.x10hosting.com http://nycdpug.x10hosting.com]. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [http://wiki.postgresql.org/images/4/46/NYCDPUG_NYISO_PostgreSQL.pdf NYCDPUG_NYISO_PostrgreSQL.pdf]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]<br />
* May 6, 2010 - Separation of Duties [http://wiki.postgresql.org/images/1/1c/SeparationofDuties.pdf NYISO_Separation_of_Duties.pdf]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=15080NYCDPUG2011-08-05T18:48:11Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group home page can be found at [http://nycdpug.x10hosting.com http://nycdpug.x10hosting.com]. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [http://wiki.postgresql.org/images/4/46/NYCDPUG_NYISO_PostgreSQL.pdf NYCDPUG_NYISO_PostrgreSQL.pdf]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]<br />
* May 6, 2010 - Separation of Duties [http://wiki.postgresql.org/images/1/1c/SeparationofDuties.pdf NYISO_Separation_of_Duties.pdf]<br />
* March 3, 2011 - Securing data in transit using SSL and SSL/TLS LDAP.</div>Rstephanhttps://wiki.postgresql.org/index.php?title=PostgreSQL_for_Oracle_DBAs&diff=15079PostgreSQL for Oracle DBAs2011-08-05T18:42:04Z<p>Rstephan: </p>
<hr />
<div>= Introduction =<br />
<br />
The following article contains information to help an Oracle DBA understand<br />
some terms and the management of a PostgreSQL database. This article is<br />
intended to be an introduction to PostgreSQL, not a tutorial or a complete<br />
definition of how to administer a PostgreSQL database. For complete<br />
documentation refer to the [http://www.postgresql.org/docs/manuals/ PostgreSQL manuals].<br />
<br />
= Oracle =<br />
<br />
== Brief description: ==<br />
<br />
* An Oracle database server consists of an Oracle instance and an Oracle database.<br />
* An Oracle instance consists of the Oracle background processes and the allocated memory within the shared global area (SGA) and the program global area (PGA).<br />
* The Oracle background processes consist of the following:<br />
** Database Writer Process (DBWn)<br />
** Log Writer Process (LGWR)<br />
** Checkpoint Process (CKPT)<br />
** System Monitor Process (SMON)<br />
** Process Monitor Process (PMON)<br />
** Recoverer Process (RECO)<br />
** Archiver Processes (ARCn)<br />
* An Oracle database consists of the database datafiles, control files, redo log files, archive log files, and parameter file.<br />
* To remotely access an Oracle database, there exists a separate process referred to as the Oracle listener.<br />
* In the Dedicated Server configuration (versus the Shared Server configuration) every established database session has its own process executing on the server.<br />
<br />
To keep things simple any comparisons with an Oracle database will always refer to a single instance managing a single database, RAC and Data Guard will not be mentioned. Note: PostgreSQL also has the concept of a warm standby (since 8.2) with the shipping of archive logs (introduced in 8.0).<br />
<br />
= PostgreSQL =<br />
<br />
== Database Server Processes ==<br />
<br />
The database server program postgres are all of the server processes. There are no separately named processes like in Oracle for the different duties within the database environment. If you were to look at the process list (ps) the name of the processes would be postgres. However, on most platforms, PostgreSQL modifies its command title so that individual server processes can readily be identified. You may need to adjust the parameters used for commands such as ps and top to show these updated titles in place of the process name ("postgres").<br />
<br />
The processes seen in a process list can be some of the following:<br />
<br />
* Master process - launches the other processes, background and session processes.<br />
* Writer process - background process that coordinates database writes, log writes and checkpoints.<br />
* Stats collector process - background process collecting information about server activity.<br />
* User session processes.<br />
<br />
The server processes communicate with each other using semaphores and shared memory to ensure data integrity throughout concurrent data access.<br />
<br />
== PostgreSQL Database Cluster ==<br />
<br />
Within a server, one or more Oracle instances can be built. The databases are separate from one another usually sharing only the Oracle listener process. PostgreSQL has the concept of a ''database cluster''. A database cluster is a collection of databases that is stored at a common file system location (the "data area"). It is possible to have multiple database clusters, so long as they use different data areas and different communication ports.<br />
<br />
The processes along with the file system components are all shared within the database cluster. All the data needed for a database cluster is stored within the cluster's data directory, commonly referred to as ''PGDATA'' (after the name of the environment variable that can be used to define it). The PGDATA directory contains several subdirectories and configuration files.<br />
<br />
The following are some of the cluster configuration files:<br />
<br />
* postgresql.conf - Parameter or main server configuration file.<br />
* pg_hba.conf - Client authentication configuration file.<br />
* pg_ident.conf - Map from OS account to PostgreSQL account file.<br />
<br />
The cluster subdirectories:<br />
<br />
* base - Subdirectorycontaining per-database subdirectories<br />
* global - Subdirectory containing cluster-wide tables<br />
** pg_auth - Authorization file containing user and role definitions.<br />
** pg_control - Control file.<br />
** pg_database - Information of databases within the cluster.<br />
* pg_clog - Subdirectory containing transaction commit status data<br />
* pg_multixact - Subdirectory containing multitransaction status data (used for shared row locks)<br />
* pg_subtrans - Subdirectory containing subtransaction status data<br />
* pg_tblspc - Subdirectory containing symbolic links to tablespaces<br />
* pg_twophase - Subdirectory containing state files for prepared transactions<br />
* pg_xlog - Subdirectory containing WAL (Write Ahead Log) files<br />
<br />
By default, for each database in the cluster there is a subdirectory within PGDATA/base, named after the database's OID (object identifier) in pg_database. This subdirectory is the default location for the database's files; in particular, its system catalogs are stored there. Each table and index is stored in a separate file, named after the table or index's filenode number, which can be found in pg_class.relfilenode.<br />
<br />
Several components that Oracle DBAs usually equate to one database are shared between databases within a PostgreSQL cluster, including the parameter file, control file, redo logs, tablespaces, accounts, roles, and background processes.<br />
<br />
== Tablespaces and Object Data Files ==<br />
<br />
PostgreSQL introduced tablespace management in version 8.0. The physical representation of a tablespace within PostgreSQL is simple: it is a directory on the file system, and the mapping is done via symbolic links.<br />
<br />
When a database is created, the default tablespace is where by default all of the database objects are stored. In Oracle this would be similar to the System, User, and Temporary tablespaces. If no default tablespace is defined during creation, the data files will go into a subdirectory of the PGDATA/base. Preferably the location of the system catalog information and the application data structures would reside in separately managed tablespaces. This is available.<br />
<br />
As in Oracle, the definition of a PostgreSQL table determines which tablespace the object resides. However, there exists no size limitation except physical boundaries placed on the device by the OS.<br />
<br />
The individual table's data is stored within a file within the tablespace (or directory). The database software will split the table across multiple datafiles in the event the table's data surpasses 1 GB.<br />
<br />
Since version 8.1, it's possible to partition a table over separate (or the same) tablespaces. This is based on PostgreSQL's table inheritance feature, using a capability of the query planner referred to as constraint exclusion.<br />
<br />
There exists no capacity for separating out specific columns (like LOBs) into separately defined tablespaces. However, in addition to the data files that represent the table (in multiples of 1 GB) there is a separation of data files for columns within a table that are TOASTed. The PostgreSQL storage system called TOAST (The Oversized-Attribute Storage Technique) automatically stores values larger than a single database page into a secondary storage area per table. The TOAST technique allows for data columns up to 1 GB in size.<br />
<br />
As in Oracle, the definition of an index determines which tablespace it resides within. Therefore, it is possible to gain the performance advantage of separating the disks that a table's data versus its indexing reside, relieving I/O contention during data manipulation.<br />
<br />
In Oracle there exists temporary tablespaces where sort information and temporary evaluation space needed for distinct statements and the like are used. PostgreSQL does not have this concept of a temporary tablespace; however it does require storage to be able to perform these activities as well. Within the "default" tablespace of the database (defined at database creation) there is a directory called pgsql_tmp. This directory holds the temporary storage needed for the evaluation. The files that get created within the directory exist only while the SQL statement is executing. They grow very fast, and are most likely not designed for space efficiency but rather speed. Be aware that disk fragmentation could result from this, and there needs to be sufficient space on the disk to support the user queries. With the release of 8.3, there are definitions of temporary tablespaces using the parameter ''temp_tablespaces''.<br />
<br />
== REDO and Archiving ==<br />
<br />
PostgreSQL uses ''Write-Ahead Logging'' (WAL) as its approach to transaction logging. WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, when log records describing the changes have been flushed to permanent storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the log records. (This is roll-forward recovery, also known as REDO.)<br />
<br />
PostgreSQL maintains its (WAL) in the ''pg_xlog'' subdirectory of the cluster's data directory.<br />
<br />
WAL was introduced into PostgreSQL in version 7.1. To maintain database consistency in case of a failure, previous releases forced all data modifications to disk before each transaction commit. With WAL, only one log file must be flushed to disk, greatly improving performance while adding capabilities like Point-In-Time Recovery and transaction archiving.<br />
<br />
A PostgreSQL system theoretically produces an indefinitely long sequence of WAL records. The system physically divides this sequence into WAL segment files, which are normally 16MB apiece. The system normally creates a few segment files and then "recycles" them by renaming no-longer-needed segment files to higher segment numbers. If you were to perform a listing of the pg_xlog directory there would always be a handful of files changing names over time.<br />
<br />
To add archiving of the WAL files there exists a parameter within the parameter file where a command is added to execute the archival process. Once this is done, Operation System "on-line" backups even become available by executing the ''pg_start_backup'' and the ''pg_stop_backup'' commands, which suspend and resume writing to the datafiles while continuing to write the transactions to the WAL files and executing the archival process.<br />
<br />
Inclusion of WAL archiving and the on-line backup commands were added in version 8.0.<br />
<br />
== Rollback or Undo ==<br />
<br />
It is interesting how the dynamic allocation of disk space is used for the storage and processing of records within tables. The files that represent the table grow as the table grows. It also grows with transactions that are performed against it. In Oracle there is a concept of rollback or undo segments that hold the information for rolling back a transaction. In PostgreSQL the data is stored within the file that represents the table. So when deletes and updates are performed on a table, the file that represents the object will contain the previous data. This space gets reused but to force recovery of used space, a maintenance process called ''vacuum'' must be executed.<br />
<br />
== Server Log File ==<br />
<br />
Oracle has the alert log file. PostgreSQL has the server log file. A configuration option would even have the connection information we normally see within the Oracle's listener.log appear in PostgreSQL's server log. The parameters within the server configuration file (postgresql.conf) determine the level, location, and name of the log file.<br />
<br />
To help with the maintenance of the server log file (it grows rapidly), there exists functionality for rotating the server log file. Parameters can be set to determine when to rotate the file based on the size or age of the file. Management of the old files is then left to the administrator.<br />
<br />
== Applications ==<br />
<br />
The command ''initdb'' creates a new PostgreSQL database cluster.<br />
<br />
The command ''psql'' starts the terminal-based front-end to PostgreSQL or SQL command prompt. Queries and commands can be executed interactively or through files. The psql command prompt has several attractive features:<br />
<br />
* Thorough on-line help for both the psql commands and the SQL syntax.<br />
* Command history and line editing.<br />
* SQL commands could exist on multiple lines and are executed only after the semi-colon (;).<br />
* Several SQL commands separated by semi-colons could be entered on a single line.<br />
* Flexible output formatting.<br />
* Multiple object description commands that are superior to Oracle's DESCRIBE.<br />
<br />
Depending on the security configurations of the environments, connections can be established locally or remotely through TCP/IP. Due to these separate security connections passwords may or may not be required to connect.<br />
<br />
The command ''pg_ctl'' is a utility for displaying status, starting, stopping, or restarting the PostgreSQL database server (postgres). Although the server can be started through the postgres executable, pg_ctl encapsulates tasks such as redirecting log output, properly detaching from the terminal and process group, and providing options for controlled shutdown.<br />
<br />
The commands ''pg_dump'' and ''pg_restore'' are utilities designed for exporting and importing the contents of a PostgreSQL database. Dumps can be output in either script or archive file formats. The script file format creates plain-text files containing the SQL commands required to reconstruct the database to the state it was at the time it was generated. The archive file format creates a file to be used with pg_restore to rebuild the database.<br />
<br />
The archive file formats are designed to be portable across architectures. Historically, any type of upgrade to the PostgreSQL software would require a pg_dump of the database prior to the upgrade. Then a pg_restore after the upgrade. Now, for minor releases (i.e., the third decimal – 8.2.x) upgrades can be done in place. However, changing versions at the first or second decimal still requires a pg_dump/pg_restore.<br />
<br />
There exists a graphical tool called [http://www.pgadmin.org/ ''pgAdmin III''] developed separately. It is distributed with the Linux and Windows versions of PostgreSQL. Connection to a database server can be established remotely to perform administrative duties. Because the tool is designed to manage all aspects of the database environment, connection to the database must be through a super user account.<br />
<br />
The pgAdmin III tool has the following standard attractive features:<br />
<br />
* Intuitive layout<br />
* Tree structure for creating and modifying database objects<br />
* Reviewing and saving of SQL when altering or creating objects<br />
<br />
[[Category:Oracle]]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=User:Rstephan&diff=12972User:Rstephan2011-01-20T18:04:01Z<p>Rstephan: New page: My name is Richard Stephan. I currently work at the [http://www.nyiso.com NYISO], and started using PostgreSQL 8.0 in 2005 for my own needs. In January of 2009, I finally convinced manag...</p>
<hr />
<div>My name is Richard Stephan. I currently work at the [http://www.nyiso.com NYISO], and started using PostgreSQL 8.0 in 2005 for my own needs. In January of 2009, I finally convinced management to use PostgreSQL instead of Oracle for one of the applications. It was successful, and others have been created since.<br />
<br />
As an advocate for PostgreSQL, I started a local users group in the Capital District of New York State - [http://nycdpug.x10hosting.com NYCDPUG].</div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=10698NYCDPUG2010-05-07T16:50:14Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group home page can be found at [http://nycdpug.x10hosting.com http://nycdpug.x10hosting.com]. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [http://wiki.postgresql.org/images/4/46/NYCDPUG_NYISO_PostgreSQL.pdf NYCDPUG_NYISO_PostrgreSQL.pdf]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]<br />
* May 6, 2010 - Separation of Duties [http://wiki.postgresql.org/images/1/1c/SeparationofDuties.pdf NYISO_Separation_of_Duties.pdf]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=10697NYCDPUG2010-05-07T16:49:35Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group home page can be found at [http://nycdpug.x10hosting.com http://nycdpug.x10hosting.com]. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [http://wiki.postgresql.org/images/4/46/NYCDPUG_NYISO_PostgreSQL.pdf NYCDPUG_NYISO_PostrgreSQL.pdf]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]<br />
* May 6, 2010 - Separation of Duties [http://wiki.postgresql.org/images/1/1c/SeparationofDuties.pdf]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=File:SeparationofDuties.pdf&diff=10696File:SeparationofDuties.pdf2010-05-07T16:48:28Z<p>Rstephan: uploaded a new version of "Image:SeparationofDuties.pdf"</p>
<hr />
<div></div>Rstephanhttps://wiki.postgresql.org/index.php?title=File:SeparationofDuties.pdf&diff=10695File:SeparationofDuties.pdf2010-05-07T16:46:41Z<p>Rstephan: </p>
<hr />
<div></div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=10694NYCDPUG2010-05-07T16:45:32Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group home page can be found at [http://nycdpug.x10hosting.com http://nycdpug.x10hosting.com]. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [http://wiki.postgresql.org/images/4/46/NYCDPUG_NYISO_PostgreSQL.pdf NYCDPUG_NYISO_PostrgreSQL.pdf]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]<br />
* May 6, 2010 - Separation of Duties</div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=10358NYCDPUG2010-03-30T16:46:11Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group home page can be found at [http://nycdpug.x10hosting.com http://nycdpug.x10hosting.com]. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [http://wiki.postgresql.org/images/4/46/NYCDPUG_NYISO_PostgreSQL.pdf NYCDPUG_NYISO_PostrgreSQL.pdf]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=10357NYCDPUG2010-03-30T16:43:14Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group home page can be found at [http://nycdpug.x10hosting.com http://nycdpug.x10hosting.com]. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL [[Image:NYCDPUG_NYISO_PostgreSQL.pdf]]<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=File:NYCDPUG_NYISO_PostgreSQL.pdf&diff=10356File:NYCDPUG NYISO PostgreSQL.pdf2010-03-30T16:38:44Z<p>Rstephan: </p>
<hr />
<div></div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=10355NYCDPUG2010-03-30T16:36:04Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group home page can be found at [http://nycdpug.x10hosting.com http://nycdpug.x10hosting.com]. The following contains a listing of past presentations:<br />
* January 7, 2010 - Introduction to PostgreSQL<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]</div>Rstephanhttps://wiki.postgresql.org/index.php?title=NYCDPUG&diff=10354NYCDPUG2010-03-30T16:29:56Z<p>Rstephan: </p>
<hr />
<div>The New York Capital District PostgreSQL Users Group home page can be found at [http://nycdpug.x10hosting.com http://nycdpug.x10hosting.com]. The following list contains a listing of presentations:<br />
* January 7, 2010 - PostgreSQL at the NYISO<br />
* March 4, 2010 - [http://momjian.us/main/writings/pgsql/inside_shmem.pdf Bruce Momjian's Inside PostgreSQL Shared Memory]</div>Rstephan