diff options
author | GitLab Bot <gitlab-bot@gitlab.com> | 2023-11-14 11:41:52 +0300 |
---|---|---|
committer | GitLab Bot <gitlab-bot@gitlab.com> | 2023-11-14 11:41:52 +0300 |
commit | 585826cb22ecea5998a2c2a4675735c94bdeedac (patch) | |
tree | 5b05f0b30d33cef48963609e8a18a4dff260eab3 /doc/administration/postgresql | |
parent | df221d036e5d0c6c0ee4d55b9c97f481ee05dee8 (diff) |
Add latest changes from gitlab-org/gitlab@16-6-stable-eev16.6.0-rc42
Diffstat (limited to 'doc/administration/postgresql')
-rw-r--r-- | doc/administration/postgresql/external.md | 7 | ||||
-rw-r--r-- | doc/administration/postgresql/external_metrics.md | 33 | ||||
-rw-r--r-- | doc/administration/postgresql/external_upgrade.md | 48 | ||||
-rw-r--r-- | doc/administration/postgresql/index.md | 5 |
4 files changed, 89 insertions, 4 deletions
diff --git a/doc/administration/postgresql/external.md b/doc/administration/postgresql/external.md index a9f857d8f00..b9bfda80b83 100644 --- a/doc/administration/postgresql/external.md +++ b/doc/administration/postgresql/external.md @@ -63,7 +63,6 @@ pg_dump: error: Error message from server: SSL SYSCALL error: EOF detected To resolve this error, ensure that you are meeting the [minimum PostgreSQL requirements](../../install/requirements.md#postgresql-requirements). After -upgrading your RDS instance to a suitable version, you should be able to perform a backup without -this error. Refer to issue #64763 -([Segmentation fault citing `LooseForeignKeys::CleanupWorker` causes complete database restart](https://gitlab.com/gitlab-org/gitlab/-/issues/364763)) -for more information. +upgrading your RDS instance to a [supported version](../../install/requirements.md#database), +you should be able to perform a backup without this error. +See [issue 64763](https://gitlab.com/gitlab-org/gitlab/-/issues/364763) for more information. diff --git a/doc/administration/postgresql/external_metrics.md b/doc/administration/postgresql/external_metrics.md new file mode 100644 index 00000000000..fc4c5652a18 --- /dev/null +++ b/doc/administration/postgresql/external_metrics.md @@ -0,0 +1,33 @@ +--- +stage: Data Stores +group: Database +info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/product/ux/technical-writing/#assignments +--- + +# Monitoring and logging setup for external databases + +External PostgreSQL database systems have different logging options for monitoring performance and troubleshooting, however they are not enabled by default. In this section we provide the recommendations for self-managed PostgreSQL, and recommendations for some major providers of PostgreSQL managed services. + +## Recommended PostgreSQL Logging settings + +You should enable the following logging settings: + +- `log_statement=ddl`: log changes of database model definition (DDL), such as `CREATE`, `ALTER` or `DROP` of objects. This helps track recent model changes that could be causing performance issues and identify security breaches and human errors. +- `log_lock_waits=on`: log of processes holding [locks](https://www.postgresql.org/docs/current/explicit-locking.html) for long periods, a common cause of poor query performance. +- `log_temp_files=0`: log usage of intense and unusual temporary files that can indicate poor query performance. +- `log_autovacuum_min_duration=0`: log all autovacuum executions. Autovacuum is a key component for overall PostgreSQL engine performance. Essential for troubleshooting and tuning if dead tuples are not being removed from tables. +- `log_min_duration_statement=1000`: log slow queries (slower than 1 second). + +The full description of the above parameter settings can be found in +[PostgreSQL error reporting and logging documentation](https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT). + +## Amazon RDS + +The Amazon Relational Database Service (RDS) provides a large number of [monitoring metrics](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Monitoring.html) and [logging interfaces](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Monitor_Logs_Events.html). Here are a few you should configure: + +- Change all above [recommended PostgreSQL Logging settings](#recommended-postgresql-logging-settings) through [RDS Parameter Groups](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithDBInstanceParamGroups.html). + - As the recommended logging parameters are [dynamic in RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Parameters.html) you don't require a reboot after changing these settings. + - The PostgreSQL logs can be observed through the [RDS console](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/logs-events-streams-console.html). +- Enable [RDS performance insight](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html) allows you to visualise your database load with many important performance metrics of a PostgreSQL database engine. +- Enable [RDS Enhanced Monitoring](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.OS.html) to monitor the operating system metrics. These metrics can indicate bottlenecks in your underlying hardware and OS that are impacting your database performance. + - In production environments set the monitoring interval to 10 seconds (or less) to capture micro bursts of resource usage that can be the cause of many performance issues. Set `Granularity=10` in the console or `monitoring-interval=10` in the CLI. diff --git a/doc/administration/postgresql/external_upgrade.md b/doc/administration/postgresql/external_upgrade.md new file mode 100644 index 00000000000..3e2c3b09853 --- /dev/null +++ b/doc/administration/postgresql/external_upgrade.md @@ -0,0 +1,48 @@ +--- +stage: Data Stores +group: Database +info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/product/ux/technical-writing/#assignments +--- + +# Upgrading external PostgreSQL databases + +When upgrading your PostgreSQL database engine, it is important to follow all steps +recommended by the PostgreSQL community and your cloud provider. Two +kinds of upgrades exist for PostgreSQL databases: + +- **Minor version upgrades**: These include only bug and security fixes. They are + always backward-compatible with your existing application database model. + + The minor version upgrade process consists of replacing the PostgreSQL binaries + and restarting the database service. The data directory remains unchanged. + +- **Major version upgrades**: These change the internal storage format and the database + catalog. As a result, object statistics used by the query optimizer + [are not transferred to the new version](https://www.postgresql.org/docs/current/pgupgrade.html) + and must be rebuilt with `ANALYZE`. + + Not following the documented major version upgrade process often results in + poor database performance and high CPU use on the database server. + +All major cloud providers support in-place major version upgrades of database +instances, using the `pg_upgrade` utility. However you must follow the pre- and +post- upgrade steps to reduce the risk of performance degradation or database disruption. + +Read carefully the major version upgrade steps of your external database platform: + +- [Amazon RDS for PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process) +- [Azure Database for PostgreSQL Flexible Server](https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-major-version-upgrade) +- [Google Cloud SQL for PostgreSQL](https://cloud.google.com/sql/docs/postgres/upgrade-major-db-version-inplace) +- [PostgreSQL community `pg_upgrade`](https://www.postgresql.org/docs/current/pgupgrade.html) + +## Always `ANALYZE` your database after a major version upgrade + +It is mandatory to run the [`ANALYZE` operation](https://www.postgresql.org/docs/current/sql-analyze.html) +to refresh the `pg_statistic` table after a major version upgrade, because optimizer statistics +[are not transferred by `pg_upgrade`](https://www.postgresql.org/docs/current/pgupgrade.html). +This should be done for all databases on the upgraded PostgreSQL service/instance/cluster. + +To speed up the `ANALYZE` operation, use the +[`vacuumdb` utility](https://www.postgresql.org/docs/current/app-vacuumdb.html), +with `--analyze-only --jobs=njobs` to execute the `ANALYZE` command in parallel by +running `njobs` commands simultaneously. diff --git a/doc/administration/postgresql/index.md b/doc/administration/postgresql/index.md index af0a86c3d72..4d73ba49846 100644 --- a/doc/administration/postgresql/index.md +++ b/doc/administration/postgresql/index.md @@ -30,6 +30,10 @@ your own external PostgreSQL server. Read how to [set up an external PostgreSQL instance](external.md). +When setting up an external database there are some metrics that are useful for monitoring and troubleshooting. +When setting up an external database there are monitoring and logging settings required for troubleshooting various database related issues. +Read more about [monitoring and logging setup for external Databases](external_metrics.md). + ### PostgreSQL replication and failover for Linux package installations **(PREMIUM SELF)** This setup is for when you have installed GitLab using the @@ -47,3 +51,4 @@ Read how to [set up PostgreSQL replication and failover](replication_and_failove - [Moving GitLab databases to a different PostgreSQL instance](moving.md) - [Multiple databases](multiple_databases.md) - [Database guides for GitLab development](../../development/database/index.md) +- [Upgrade external database](external_upgrade.md) |