Version v2.7.0 of the documentation is no longer actively maintained. The site that you are currently viewing is an archived snapshot. For up-to-date documentation, see the latest version.
Category: Admin
-
SchemaSPY: Dump & Visualize PG Schema
Use the following docker command to generate a database schema report, using CMDB as an example: docker run -v /www/schema/pg-meta/meta/pigsty:/output andrewjones/schemaspy-postgres:latest -host 10.10.10.10 -port 5432 -u dbuser_dba -p DBUser.DBA -db …
Use the following docker command to generate a database schema report, using CMDB as an example: docker run -v /www/schema/pg-meta/meta/pigsty:/output andrewjones/schemaspy-postgres:latest -host 10.10.10.10 -port 5432 -u dbuser_dba -p DBUser.DBA -db …
-
ByteBase: PG Schema Migration
ByteBase ByteBase is a database schema change management tool, which is a tool for database schema changes. The following command will start a ByteBase on the meta node 8887 port by default. mkdir -p /data/bytebase/data; docker run --init --name …
ByteBase ByteBase is a database schema change management tool, which is a tool for database schema changes. The following command will start a ByteBase on the meta node 8887 port by default. mkdir -p /data/bytebase/data; docker run --init --name …
-
PGAdmin4: PG Admin GUI Tool
pgAdmin4 is a useful PostgreSQL management tool. Execute the following command to launch the pgadmin service on the admin node: cd ~/pigsty/app/pgadmin ; docker compose up -d The default port for pgadmin is 8885, and you can access it through the …
pgAdmin4 is a useful PostgreSQL management tool. Execute the following command to launch the pgadmin service on the admin node: cd ~/pigsty/app/pgadmin ; docker compose up -d The default port for pgadmin is 8885, and you can access it through the …
-
Docker: Container Support & Proxy
Pigsty has a DOCKER module, which provides a set of playbooks to install and manage Docker on the target nodes. This document will guide you through how to enable Docker support in Pigsty, and how to configure a proxy server for DockerHub. Install …
Pigsty has a DOCKER module, which provides a set of playbooks to install and manage Docker on the target nodes. This document will guide you through how to enable Docker support in Pigsty, and how to configure a proxy server for DockerHub. Install …
-
Nginx: Expose Web Service
Pigsty will install Nginx on INFRA Node, as a Web service proxy. Nginx is the access entry for all WebUI services of Pigsty, and it defaults to the use the 80/443 port on INFRA nodes. Pigsty provides a global parameter infra_portal to configure Nginx …
Pigsty will install Nginx on INFRA Node, as a Web service proxy. Nginx is the access entry for all WebUI services of Pigsty, and it defaults to the use the 80/443 port on INFRA nodes. Pigsty provides a global parameter infra_portal to configure Nginx …
-
PGWeb: Browser-based PG Client
PGWEB: https://github.com/sosedoff/pgweb Simple web-based and cross-platform PostgreSQL database explorer. Public Demo: http://cli.pigsty.cc TL; DR cd ~/pigsty/app/pgweb ; make up Visit http://cli.pigsty or http://10.10.10.10:8886 Try connecting with …
PGWEB: https://github.com/sosedoff/pgweb Simple web-based and cross-platform PostgreSQL database explorer. Public Demo: http://cli.pigsty.cc TL; DR cd ~/pigsty/app/pgweb ; make up Visit http://cli.pigsty or http://10.10.10.10:8886 Try connecting with …
-
Use PostgreSQL as Grafana Backend
Grafana uses SQLite for its metadata by default. Pigsty v2.7 can provision a PostgreSQL user and database for Grafana, after which Grafana can be pointed at that database manually. v2.7 scope The old grafana_database and grafana_pgurl Pigsty …
Grafana uses SQLite for its metadata by default. Pigsty v2.7 can provision a PostgreSQL user and database for Grafana, after which Grafana can be pointed at that database manually. v2.7 scope The old grafana_database and grafana_pgurl Pigsty …
-
Services & Access
Personal User Service is meaningless to personal users. You can access the database with raw IP address or whatever method you like. psql postgres://dbuser_dba:[email protected]/meta # dbsu direct connect psql …
Personal User Service is meaningless to personal users. You can access the database with raw IP address or whatever method you like. psql postgres://dbuser_dba:[email protected]/meta # dbsu direct connect psql …
-
Provisioning
Pigsty runs on nodes, which are Bare Metals or Virtual Machines. You can prepare them manually, or using terraform & vagrant for provisioning. Sandbox Pigsty has a sandbox, which is a 4-node deployment with fixed IP addresses and other identifiers. …
Pigsty runs on nodes, which are Bare Metals or Virtual Machines. You can prepare them manually, or using terraform & vagrant for provisioning. Sandbox Pigsty has a sandbox, which is a 4-node deployment with fixed IP addresses and other identifiers. …
-
Point-in-Time Recovery
Overview You can roll back your cluster to any point in time, avoiding data loss caused by software defects and human errors. Pigsty’s PostgreSQL clusters come with an automatically configured Point in Time Recovery (PITR) solution, based on the …
Overview You can roll back your cluster to any point in time, avoiding data loss caused by software defects and human errors. Pigsty’s PostgreSQL clusters come with an automatically configured Point in Time Recovery (PITR) solution, based on the …
-
Migration
Pigsty has a built-in playbook pgsql-migration.yml to perform online database migration based on logical replication. With proper automation, the downtime could be minimized to several seconds. But beware that logical replication requires PostgreSQL …
Pigsty has a built-in playbook pgsql-migration.yml to perform online database migration based on logical replication. With proper automation, the downtime could be minimized to several seconds. But beware that logical replication requires PostgreSQL …
-
Backup & PITR
Pigsty uses pgBackRest for PITR backup & restore. In the case of a hardware failure, a physical replica failover could be the best choice. Whereas for data corruption scenarios (whether machine or human errors), Point-in-Time Recovery (PITR) is …
Pigsty uses pgBackRest for PITR backup & restore. In the case of a hardware failure, a physical replica failover could be the best choice. Whereas for data corruption scenarios (whether machine or human errors), Point-in-Time Recovery (PITR) is …
-
Bind a L2 VIP to PostgreSQL Primary with VIP-Manager
You can define an OPTIONAL L2 VIP on a PostgreSQL cluster, provided that all nodes in the cluster are in the same L2 network. This VIP works on Master-Backup mode and always points to the node where the primary instance of the database cluster is …
You can define an OPTIONAL L2 VIP on a PostgreSQL cluster, provided that all nodes in the cluster are in the same L2 network. This VIP works on Master-Backup mode and always points to the node where the primary instance of the database cluster is …
-
Administration
How to maintain an existing PostgreSQL cluster with Pigsty? Here are some SOP for common pgsql admin tasks Case 1: Create Cluster Case 2: Create User Case 3: Create Database Case 4: Reload Service Case 5: Reload HBARule Case 6: Config Cluster Case …
How to maintain an existing PostgreSQL cluster with Pigsty? Here are some SOP for common pgsql admin tasks Case 1: Create Cluster Case 2: Create User Case 3: Create Database Case 4: Reload Service Case 5: Reload HBARule Case 6: Config Cluster Case …
-
Bind a L2 VIP to Node Cluster with Keepalived
You can bind an optional L2 VIP on a node cluster with vip_enabled. proxy: hosts: 10.10.10.29: { nodename: proxy-1 } 10.10.10.30: { nodename: proxy-2 } # , vip_role: master } vars: node_cluster: proxy vip_enabled: true vip_vrid: 128 vip_address: …
You can bind an optional L2 VIP on a node cluster with vip_enabled. proxy: hosts: 10.10.10.29: { nodename: proxy-1 } 10.10.10.30: { nodename: proxy-2 } # , vip_role: master } vars: node_cluster: proxy vip_enabled: true vip_vrid: 128 vip_address: …
-
Planning
Pigsty v2.7 planning starts by selecting a deployment topology and preparing the matching hosts, operating systems, network, and administrator access. Planning Checklist Choose a single-node, monitoring-only, or highly available topology from the …
Pigsty v2.7 planning starts by selecting a deployment topology and preparing the matching hosts, operating systems, network, and administrator access. Planning Checklist Choose a single-node, monitoring-only, or highly available topology from the …
-
Use PostgreSQL as Ansible Inventory CMDB
Pigsty v2.7 normally reads its Ansible inventory from pigsty.yml. The release also ships three scripts that can load that inventory into the pigsty schema of the meta database and switch Ansible to a generated dynamic inventory. The implementation in …
Pigsty v2.7 normally reads its Ansible inventory from pigsty.yml. The release also ships three scripts that can load that inventory into the pigsty schema of the meta database and switch Ansible to a generated dynamic inventory. The implementation in …
-
Preparation
Node Pigsty supports the Linux kernel and x86_64/amd64 arch, applicable to any node. A “node” refers to a resource that is SSH accessible and offers a bare OS environment, such as a physical machine, a virtual machine, or an OS container equipped …
Node Pigsty supports the Linux kernel and x86_64/amd64 arch, applicable to any node. A “node” refers to a resource that is SSH accessible and offers a bare OS environment, such as a physical machine, a virtual machine, or an OS container equipped …
-
Compatibility
Overview Pigsty recommends using Linux kernel, amd64 arch, and RockyLinux 8.9, Debian 12 or Ubuntu 22.04 as base OS. Kernel Architecture Compatibility: Linux kernel, amd64 architecture (x86_64) EL Distribution Support: EL7, EL8, EL9; (RHEL, Rocky, …
Overview Pigsty recommends using Linux kernel, amd64 arch, and RockyLinux 8.9, Debian 12 or Ubuntu 22.04 as base OS. Kernel Architecture Compatibility: Linux kernel, amd64 architecture (x86_64) EL Distribution Support: EL7, EL8, EL9; (RHEL, Rocky, …