7 files changed, 92 insertions, 19 deletions
diff --git a/.gitignore b/.gitignore
index 6b9257b5c9..fe137f3370 100644
--- a/.gitignore
+++ b/.gitignore
@@ -40,6 +40,7 @@ __pycache__/
 /.coverage*
 /.mypy_cache/
 /.tox
+/.tox-pg-container
 /build/
 /coverage.*
 /dist/
diff --git a/changelog.d/10906.misc b/changelog.d/10906.misc
new file mode 100644
index 0000000000..20a1cbfbd0
--- /dev/null
+++ b/changelog.d/10906.misc
@@ -0,0 +1 @@
+Update development testing script `test_postgresql.sh` to use a supported Python version and make re-runs quicker.
\ No newline at end of file
diff --git a/docker/Dockerfile-pgtests b/docker/Dockerfile-pgtests
index 3bfee845c6..92b804d193 100644
--- a/docker/Dockerfile-pgtests
+++ b/docker/Dockerfile-pgtests
@@ -1,6 +1,6 @@
 # Use the Sytest image that comes with a lot of the build dependencies
 # pre-installed
-FROM matrixdotorg/sytest:latest
+FROM matrixdotorg/sytest:bionic
 
 # The Sytest image doesn't come with python, so install that
 RUN apt-get update && apt-get -qq install -y python3 python3-dev python3-pip
@@ -8,5 +8,23 @@ RUN apt-get update && apt-get -qq install -y python3 python3-dev python3-pip
 # We need tox to run the tests in run_pg_tests.sh
 RUN python3 -m pip install tox
 
-ADD run_pg_tests.sh /pg_tests.sh
-ENTRYPOINT /pg_tests.sh
+# Initialise the db
+RUN su -c '/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/data -E "UTF-8" --lc-collate="C.UTF-8" --lc-ctype="C.UTF-8" --username=postgres' postgres
+
+# Add a user with our UID and GID so that files get created on the host owned
+# by us, not root.
+ARG UID
+ARG GID
+RUN groupadd --gid $GID user
+RUN useradd --uid $UID --gid $GID --groups sudo --no-create-home user
+
+# Ensure we can start postgres by sudo-ing as the postgres user.
+RUN apt-get update && apt-get -qq install -y sudo
+RUN echo "user ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers
+
+ADD run_pg_tests.sh /run_pg_tests.sh
+# Use the "exec form" of ENTRYPOINT (https://docs.docker.com/engine/reference/builder/#entrypoint)
+# so that we can `docker run` this container and pass arguments to pg_tests.sh
+ENTRYPOINT ["/run_pg_tests.sh"]
+
+USER user
diff --git a/docker/run_pg_tests.sh b/docker/run_pg_tests.sh
index 1fd08cb62b..58e2177d34 100755
--- a/docker/run_pg_tests.sh
+++ b/docker/run_pg_tests.sh
@@ -10,11 +10,10 @@ set -e
 # Set PGUSER so Synapse's tests know what user to connect to the database with
 export PGUSER=postgres
 
-# Initialise & start the database
-su -c '/usr/lib/postgresql/9.6/bin/initdb -D /var/lib/postgresql/data -E "UTF-8" --lc-collate="en_US.UTF-8" --lc-ctype="en_US.UTF-8" --username=postgres' postgres
-su -c '/usr/lib/postgresql/9.6/bin/pg_ctl -w -D /var/lib/postgresql/data start' postgres
+# Start the database
+sudo -u postgres /usr/lib/postgresql/10/bin/pg_ctl -w -D /var/lib/postgresql/data start
 
 # Run the tests
 cd /src
 export TRIAL_FLAGS="-j 4"
-tox --workdir=/tmp -e py35-postgres
+tox --workdir=./.tox-pg-container -e py36-postgres "$@"
diff --git a/docs/development/contributing_guide.md b/docs/development/contributing_guide.md
index 97352b0f26..713366368c 100644
--- a/docs/development/contributing_guide.md
+++ b/docs/development/contributing_guide.md
@@ -170,6 +170,53 @@ To increase the log level for the tests, set `SYNAPSE_TEST_LOG_LEVEL`:
 SYNAPSE_TEST_LOG_LEVEL=DEBUG trial tests
 ```
 
+### Running tests under PostgreSQL
+
+Invoking `trial` as above will use an in-memory SQLite database. This is great for
+quick development and testing. However, we recommend using a PostgreSQL database
+in production (and indeed, we have some code paths specific to each database).
+This means that we need to run our unit tests against PostgreSQL too. Our CI does
+this automatically for pull requests and release candidates, but it's sometimes
+useful to reproduce this locally.
+
+To do so, [configure Postgres](../postgres.md) and run `trial` with the
+following environment variables matching your configuration:
+
+- `SYNAPSE_POSTGRES` to anything nonempty
+- `SYNAPSE_POSTGRES_HOST`
+- `SYNAPSE_POSTGRES_USER`
+- `SYNAPSE_POSTGRES_PASSWORD`
+
+For example:
+
+```shell
+export SYNAPSE_POSTGRES=1
+export SYNAPSE_POSTGRES_HOST=localhost
+export SYNAPSE_POSTGRES_USER=postgres
+export SYNAPSE_POSTGRES_PASSWORD=mydevenvpassword
+trial
+```
+
+#### Prebuilt container
+
+Since configuring PostgreSQL can be fiddly, we can make use of a pre-made
+Docker container to set up PostgreSQL and run our tests for us. To do so, run
+
+```shell
+scripts-dev/test_postgresql.sh
+```
+
+Any extra arguments to the script will be passed to `tox` and then to `trial`,
+so we can run a specific test in this container with e.g.
+
+```shell
+scripts-dev/test_postgresql.sh tests.replication.test_sharded_event_persister.EventPersisterShardTestCase
+```
+
+The container creates a folder in your Synapse checkout called
+`.tox-pg-container` and uses this as a tox environment. The output of any
+`trial` runs goes into `_trial_temp` in your synapse source directory — the same
+as running `trial` directly on your host machine.
 
 ## Run the integration tests ([Sytest](https://github.com/matrix-org/sytest)).
 
diff --git a/scripts-dev/test_postgresql.sh b/scripts-dev/test_postgresql.sh
new file mode 100755
index 0000000000..43cfa256e4
--- /dev/null
+++ b/scripts-dev/test_postgresql.sh
@@ -0,0 +1,19 @@
+#!/usr/bin/env bash
+
+# This script builds the Docker image to run the PostgreSQL tests, and then runs
+# the tests. It uses a dedicated tox environment so that we don't have to
+# rebuild it each time.
+
+# Command line arguments to this script are forwarded to "tox" and then to "trial".
+
+set -e
+
+# Build, and tag
+docker build docker/ \
+  --build-arg "UID=$(id -u)" \
+  --build-arg "GID=$(id -g)" \
+  -f docker/Dockerfile-pgtests \
+  -t synapsepgtests
+
+# Run, mounting the current directory into /src
+docker run --rm -it -v "$(pwd):/src" -v synapse-pg-test-tox:/tox synapsepgtests "$@"
diff --git a/test_postgresql.sh b/test_postgresql.sh
deleted file mode 100755
index c10828fbbc..0000000000
--- a/test_postgresql.sh
+++ /dev/null
@@ -1,12 +0,0 @@
-#!/usr/bin/env bash
-
-# This script builds the Docker image to run the PostgreSQL tests, and then runs
-# the tests.
-
-set -e
-
-# Build, and tag
-docker build docker/ -f docker/Dockerfile-pgtests -t synapsepgtests
-
-# Run, mounting the current directory into /src
-docker run --rm -it -v $(pwd)\:/src synapsepgtests