Skip to content

Latest commit

 

History

History
307 lines (227 loc) · 6.33 KB

Linux.md

File metadata and controls

307 lines (227 loc) · 6.33 KB
Raw

PgHero for Linux

Distributions

64-bit only

Installation

Ubuntu 18.04 (Bionic)

wget -qO- https://dl.packager.io/srv/pghero/pghero/key | sudo apt-key add -
sudo wget -O /etc/apt/sources.list.d/pghero.list \
  https://dl.packager.io/srv/pghero/pghero/master/installer/ubuntu/18.04.repo
sudo apt-get update
sudo apt-get -y install pghero

Ubuntu 16.04 (Xenial)

wget -qO- https://dl.packager.io/srv/pghero/pghero/key | sudo apt-key add -
sudo wget -O /etc/apt/sources.list.d/pghero.list \
  https://dl.packager.io/srv/pghero/pghero/master/installer/ubuntu/16.04.repo
sudo apt-get update
sudo apt-get -y install pghero

Ubuntu 14.04 (Trusty)

wget -qO- https://dl.packager.io/srv/pghero/pghero/key | sudo apt-key add -
sudo wget -O /etc/apt/sources.list.d/pghero.list \
  https://dl.packager.io/srv/pghero/pghero/master/installer/ubuntu/14.04.repo
sudo apt-get update
sudo apt-get -y install pghero

Debian 9 (Stretch)

sudo apt-get -y install apt-transport-https
wget -qO- https://dl.packager.io/srv/pghero/pghero/key | sudo apt-key add -
sudo wget -O /etc/apt/sources.list.d/pghero.list \
  https://dl.packager.io/srv/pghero/pghero/master/installer/debian/9.repo
sudo apt-get update
sudo apt-get -y install pghero

Debian 8 (Jesse)

sudo apt-get -y install apt-transport-https
wget -qO- https://dl.packager.io/srv/pghero/pghero/key | sudo apt-key add -
sudo wget -O /etc/apt/sources.list.d/pghero.list \
  https://dl.packager.io/srv/pghero/pghero/master/installer/debian/8.repo
sudo apt-get update
sudo apt-get -y install pghero

Debian 7 (Wheezy)

sudo apt-get -y install apt-transport-https
wget -qO- https://dl.packager.io/srv/pghero/pghero/key | sudo apt-key add -
sudo wget -O /etc/apt/sources.list.d/pghero.list \
  https://dl.packager.io/srv/pghero/pghero/master/installer/debian/7.repo
sudo apt-get update
sudo apt-get -y install pghero

CentOS / RHEL 7

sudo wget -O /etc/yum.repos.d/pghero.repo \
  https://dl.packager.io/srv/pghero/pghero/master/installer/el/7.repo
sudo yum -y install pghero

SUSE Linux Enterprise Server 12

sudo wget -O /etc/zypp/repos.d/pghero.repo \
  https://dl.packager.io/srv/pghero/pghero/master/installer/sles/12.repo
sudo zypper install pghero

Setup

Add your database.

sudo pghero config:set DATABASE_URL=postgres://user:password@hostname:5432/dbname

And optional authentication.

sudo pghero config:set PGHERO_USERNAME=link
sudo pghero config:set PGHERO_PASSWORD=hyrule

Start the server

sudo pghero config:set PORT=3001
sudo pghero config:set RAILS_LOG_TO_STDOUT=disabled
sudo pghero scale web=1

Confirm it’s running with:

curl -v http://localhost:3001/

To open to the outside world, add a proxy. Here’s how to do it with Nginx on Ubuntu.

sudo apt-get install -y nginx
cat | sudo tee /etc/nginx/sites-available/default <<EOF
server {
  listen          80;
  server_name     "";
  location / {
    proxy_pass    http://localhost:3001;
  }
}
EOF
sudo service nginx restart

Management

sudo service pghero status
sudo service pghero start
sudo service pghero stop
sudo service pghero restart

View logs

sudo pghero logs

Query Stats

Query stats can be enabled from the dashboard. If you run into issues, view the guide.

Historical Query Stats

To track query stats over time, create a table to store them.

CREATE TABLE "pghero_query_stats" (
  "id" serial primary key,
  "database" text,
  "user" text,
  "query" text,
  "query_hash" bigint,
  "total_time" float,
  "calls" bigint,
  "captured_at" timestamp
);
CREATE INDEX ON "pghero_query_stats" ("database", "captured_at");

This table can be in the current database or another database. If another database, run:

sudo pghero config:set PGHERO_STATS_DATABASE_URL=...

Schedule the task below to run every 5 minutes.

sudo pghero run rake pghero:capture_query_stats

After this, a time range slider will appear on the Queries tab.

Historical Space Stats

To track space stats over time, create a table to store them.

CREATE TABLE "pghero_space_stats" (
  "id" serial primary key,
  "database" text,
  "schema" text,
  "relation" text,
  "size" bigint,
  "captured_at" timestamp
);
CREATE INDEX ON "pghero_space_stats" ("database", "captured_at");

Schedule the task below to run once a day.

sudo pghero run rake pghero:capture_space_stats

System Stats

CPU usage is available for Amazon RDS. Add these variables to your environment:

sudo pghero config:set PGHERO_ACCESS_KEY_ID=accesskey123
sudo pghero config:set PGHERO_SECRET_ACCESS_KEY=secret123
sudo pghero config:set PGHERO_DB_INSTANCE_IDENTIFIER=epona

Multiple Databases

Create a pghero.yml with:

databases:
  primary:
    url: postgres://...
  replica:
    url: postgres://...

And run:

cat pghero.yml | sudo pghero run sh -c "cat > config/pghero.yml"
sudo service pghero restart

Permissions

We recommend setting up a dedicated user for PgHero.

Customize

Minimum time for long running queries

sudo pghero config:set PGHERO_LONG_RUNNING_QUERY_SEC=60 # default

Minimum average time for slow queries

sudo pghero config:set PGHERO_SLOW_QUERY_MS=20 # default

Minimum calls for slow queries

sudo pghero config:set PGHERO_SLOW_QUERY_CALLS=100 # default

Minimum connections for high connections warning

sudo pghero config:set PGHERO_TOTAL_CONNECTIONS_THRESHOLD=100 # default

Statement timeout for explain

sudo pghero config:set PGHERO_EXPLAIN_TIMEOUT_SEC=10 # default

Upgrading

Ubuntu and Debian

sudo apt-get update
sudo apt-get install --only-upgrade pghero
sudo service pghero restart

CentOS and RHEL

sudo yum update
sudo yum install pghero
sudo service pghero restart

SUSE

sudo zypper update pghero
sudo service pghero restart

Credits

❤️ Made possible by Packager