Admin
Logging
PostgREST logs basic request information to stdout
, including the authenticated user if available, the requesting IP address and user agent, the URL requested, and HTTP response status.
127.0.0.1 - user [26/Jul/2021:01:56:38 -0500] "GET /clients HTTP/1.1" 200 - "" "curl/7.64.0"
127.0.0.1 - anonymous [26/Jul/2021:01:56:48 -0500] "GET /unexistent HTTP/1.1" 404 - "" "curl/7.64.0"
For diagnostic information about the server itself, PostgREST logs to stderr
.
12/Jun/2021:17:47:39 -0500: Attempting to connect to the database...
12/Jun/2021:17:47:39 -0500: Listening on port 3000
12/Jun/2021:17:47:39 -0500: Connection successful
12/Jun/2021:17:47:39 -0500: Config re-loaded
12/Jun/2021:17:47:40 -0500: Schema cache loaded
Note
When running it in an SSH session you must detach it from stdout or it will be terminated when the session closes. The easiest technique is redirecting the output to a log file or to the syslog:
ssh foo@example.com \
'postgrest foo.conf </dev/null >/var/log/postgrest.log 2>&1 &'
# another option is to pipe the output into "logger -t postgrest"
Currently PostgREST doesn’t log the SQL commands executed against the underlying database.
Database Logs
To find the SQL operations, you can watch the database logs. By default PostgreSQL does not keep these logs, so you’ll need to make the configuration changes below.
Find postgresql.conf
inside your PostgreSQL data directory (to find that, issue the command show data_directory;
). Either find the settings scattered throughout the file and change them to the following values, or append this block of code to the end of the configuration file.
# send logs where the collector can access them
log_destination = "stderr"
# collect stderr output to log files
logging_collector = on
# save logs in pg_log/ under the pg data directory
log_directory = "pg_log"
# (optional) new log file per day
log_filename = "postgresql-%Y-%m-%d.log"
# log every kind of SQL statement
log_statement = "all"
Restart the database and watch the log file in real-time to understand how HTTP requests are being translated into SQL commands.
Note
On Docker you can enable the logs by using a custom init.sh
:
#!/bin/sh
echo "log_statement = 'all'" >> /var/lib/postgresql/data/postgresql.conf
After that you can start the container and check the logs with docker logs
.
docker run -v "$(pwd)/init.sh":"/docker-entrypoint-initdb.d/init.sh" -d postgres
docker logs -f <container-id>
Server Version
When debugging a problem it’s important to verify the PostgREST version. Look for the Server
HTTP response header, which contains the version number.
Server: postgrest/11.0.1
Trace Header
You can enable tracing HTTP requests by setting server-trace-header. Specify the set header in the request, and the server will include it in the response.
server-trace-header = "X-Request-Id"
GET /users HTTP/1.1
X-Request-Id: 123
curl "http://localhost:3000/users" \
-H "X-Request-Id: 123"
HTTP/1.1 200 OK
X-Request-Id: 123
Execution plan
You can get the EXPLAIN execution plan of a request by adding the Accept: application/vnd.pgrst.plan
header.
This is enabled by db-plan-enabled (false by default).
GET /users?select=name&order=id HTTP/1.1
Accept: application/vnd.pgrst.plan
curl "http://localhost:3000/users?select=name&order=id" \
-H "Accept: application/vnd.pgrst.plan"
Aggregate (cost=73.65..73.68 rows=1 width=112)
-> Index Scan using users_pkey on users (cost=0.15..60.90 rows=850 width=36)
The output of the plan is generated in text
format by default but you can change it to JSON by using the +json
suffix.
GET /users?select=name&order=id HTTP/1.1
Accept: application/vnd.pgrst.plan+json
curl "http://localhost:3000/users?select=name&order=id" \
-H "Accept: application/vnd.pgrst.plan+json"
[
{
"Plan": {
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 73.65,
"Total Cost": 73.68,
"Plan Rows": 1,
"Plan Width": 112,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "users_pkey",
"Relation Name": "users",
"Alias": "users",
"Startup Cost": 0.15,
"Total Cost": 60.90,
"Plan Rows": 850,
"Plan Width": 36
}
]
}
}
]
By default the plan is assumed to generate the JSON representation of a resource(application/json
), but you can obtain the plan for the different representations that PostgREST supports by adding them to the for
parameter. For instance, to obtain the plan for a text/xml
, you would use Accept: application/vnd.pgrst.plan; for="text/xml
.
The other available parameters are analyze
, verbose
, settings
, buffers
and wal
, which correspond to the EXPLAIN command options. To use the analyze
and wal
parameters for example, you would add them like Accept: application/vnd.pgrst.plan; options=analyze|wal
.
Note that akin to the EXPLAIN command, the changes will be committed when using the analyze
option. To avoid this, you can use the db-tx-end and the Prefer: tx=rollback
header.
Health Check
You can enable a health check to verify if PostgREST is available for client requests. Also to check the status of its internal state.
To do this, set the configuration variable admin-server-port to the port number of your preference. Two endpoints live
and ready
will then be available.
The live
endpoint verifies if PostgREST is running on its configured port. A request will return 200 OK
if PostgREST is alive or 503
otherwise.
The ready
endpoint also checks the state of both the Database Connection and the Schema Cache. A request will return 200 OK
if it is ready or 503
if not.
For instance, to verify if PostgREST is running at localhost:3000
while the admin-server-port
is set to 3001
:
GET localhost:3001/live HTTP/1.1
curl -I "http://localhost:3001/live"
HTTP/1.1 200 OK
If you have a machine with multiple network interfaces and multiple PostgREST instances in the same port, you need to specify a unique hostname in the configuration of each PostgREST instance for the health check to work correctly. Don’t use the special values(!4
, *
, etc) in this case because the health check could report a false positive.