README.md in pgslice-0.4.8 vs README.md in pgslice-0.5.0
- old
+ new
@@ -12,11 +12,11 @@
```sh
gem install pgslice
```
-This will give you the `pgslice` command. You can also install it with [Homebrew](#homebrew). If installation fails, you may need to install [dependencies](#dependencies).
+This will give you the `pgslice` command. You can also install it with [Homebrew](#homebrew) or [Docker](#docker). If installation fails, you may need to install [dependencies](#dependencies).
## Steps
1. Ensure the table you want to partition has been created. We’ll refer to this as `<table>`.
@@ -106,21 +106,21 @@
```
```sql
BEGIN;
-CREATE TABLE "public"."visits_201808" PARTITION OF "public"."visits_intermediate" FOR VALUES FROM ('2018-08-01') TO ('2018-09-01');
+CREATE TABLE "public"."visits_202108" PARTITION OF "public"."visits_intermediate" FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
-ALTER TABLE "public"."visits_201808" ADD PRIMARY KEY ("id");
+ALTER TABLE "public"."visits_202108" ADD PRIMARY KEY ("id");
-CREATE TABLE "public"."visits_201809" PARTITION OF "public"."visits_intermediate" FOR VALUES FROM ('2018-09-01') TO ('2018-10-01');
+CREATE TABLE "public"."visits_202109" PARTITION OF "public"."visits_intermediate" FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');
-ALTER TABLE "public"."visits_201809" ADD PRIMARY KEY ("id");
+ALTER TABLE "public"."visits_202109" ADD PRIMARY KEY ("id");
-CREATE TABLE "public"."visits_201810" PARTITION OF "public"."visits_intermediate" FOR VALUES FROM ('2018-10-01') TO ('2018-11-01');
+CREATE TABLE "public"."visits_202110" PARTITION OF "public"."visits_intermediate" FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
-ALTER TABLE "public"."visits_201808" ADD PRIMARY KEY ("id");
+ALTER TABLE "public"."visits_202110" ADD PRIMARY KEY ("id");
COMMIT;
```
```sh
@@ -129,33 +129,33 @@
```sql
/* 1 of 3 */
INSERT INTO "public"."visits_intermediate" ("id", "user_id", "ip", "created_at")
SELECT "id", "user_id", "ip", "created_at" FROM "public"."visits"
- WHERE "id" > 0 AND "id" <= 10000 AND "created_at" >= '2018-08-01'::date AND "created_at" < '2018-11-01'::date
+ WHERE "id" > 0 AND "id" <= 10000 AND "created_at" >= '2021-08-01'::date AND "created_at" < '2021-11-01'::date
/* 2 of 3 */
INSERT INTO "public"."visits_intermediate" ("id", "user_id", "ip", "created_at")
SELECT "id", "user_id", "ip", "created_at" FROM "public"."visits"
- WHERE "id" > 10000 AND "id" <= 20000 AND "created_at" >= '2018-08-01'::date AND "created_at" < '2018-11-01'::date
+ WHERE "id" > 10000 AND "id" <= 20000 AND "created_at" >= '2021-08-01'::date AND "created_at" < '2021-11-01'::date
/* 3 of 3 */
INSERT INTO "public"."visits_intermediate" ("id", "user_id", "ip", "created_at")
SELECT "id", "user_id", "ip", "created_at" FROM "public"."visits"
- WHERE "id" > 20000 AND "id" <= 30000 AND "created_at" >= '2018-08-01'::date AND "created_at" < '2018-11-01'::date
+ WHERE "id" > 20000 AND "id" <= 30000 AND "created_at" >= '2021-08-01'::date AND "created_at" < '2021-11-01'::date
```
```sh
pgslice analyze visits
```
```sql
-ANALYZE VERBOSE "public"."visits_201808";
+ANALYZE VERBOSE "public"."visits_202108";
-ANALYZE VERBOSE "public"."visits_201809";
+ANALYZE VERBOSE "public"."visits_202109";
-ANALYZE VERBOSE "public"."visits_201810";
+ANALYZE VERBOSE "public"."visits_202110";
ANALYZE VERBOSE "public"."visits_intermediate";
```
```sh
@@ -215,18 +215,18 @@
## Archiving Partitions
Back up and drop older partitions each day, month, or year.
```sh
-pg_dump -c -Fc -t <table>_201809 $PGSLICE_URL > <table>_201809.dump
-psql -c "DROP TABLE <table>_201809" $PGSLICE_URL
+pg_dump -c -Fc -t <table>_202109 $PGSLICE_URL > <table>_202109.dump
+psql -c "DROP TABLE <table>_202109" $PGSLICE_URL
```
If you use [Amazon S3](https://aws.amazon.com/s3/) for backups, [s3cmd](https://github.com/s3tools/s3cmd) is a nice tool.
```sh
-s3cmd put <table>_201809.dump s3://<s3-bucket>/<table>_201809.dump
+s3cmd put <table>_202109.dump s3://<s3-bucket>/<table>_202109.dump
```
## Schema Updates
Once a table is partitioned, make schema updates on the master table only (not partitions). This includes adding, removing, and modifying columns, as well as adding and removing indexes and foreign keys.
@@ -270,11 +270,11 @@
SELECT * FROM
visits
WHERE
user_id = 123 AND
-- for performance
- created_at >= '2018-09-01' AND created_at < '2018-09-02'
+ created_at >= '2021-09-01' AND created_at < '2021-09-02'
```
For this to be effective, ensure `constraint_exclusion` is set to `partition` (default value) or `on`.
```sql
@@ -334,26 +334,39 @@
## Data Protection
Always make sure your [connection is secure](https://ankane.org/postgres-sslmode-explained) when connecting to a database over a network you don’t fully trust. Your best option is to connect over SSH or a VPN. Another option is to use `sslmode=verify-full`. If you don’t do this, your database credentials can be compromised.
-## Homebrew
+## Additional Installation Methods
-On Mac, you can use:
+### Homebrew
+With Homebrew, you can use:
+
```sh
brew install ankane/brew/pgslice
```
+### Docker
+
+Get the [Docker image](https://hub.docker.com/r/ankane/pgslice) with:
+
+```sh
+docker pull ankane/pgslice
+alias pgslice="docker run --rm -e PGSLICE_URL ankane/pgslice"
+```
+
+This will give you the `pgslice` command.
+
## Dependencies
If installation fails, your system may be missing Ruby or libpq.
On Mac, run:
```sh
-brew install postgresql
+brew install libpq
```
On Ubuntu, run:
```sh
@@ -372,18 +385,9 @@
```sh
gem install specific_install
gem specific_install https://github.com/ankane/pgslice.git
```
-
-## Docker
-
-```sh
-docker build -t pgslice .
-alias pgslice="docker run --rm -e PGSLICE_URL pgslice"
-```
-
-This will give you the `pgslice` command.
## Reference
- [PostgreSQL Manual](https://www.postgresql.org/docs/current/static/ddl-partitioning.html)
- [PostgreSQL Wiki](https://wiki.postgresql.org/wiki/Table_partitioning)