Open itroot opened 2 years ago
@itroot we are connecting to postgres
database and then issuing this command CREATE DATABASE test_42 WITH template = postgres;
Note that the template DB to use is postgres
itself. Kinda non-standard way of connecting to the same database and using it as a template as well. The documentation at https://www.postgresql.org/docs/current/manage-ag-templatedbs.html mentions:
The principal limitation is that no other sessions can be connected to the source database while it is being copied.
@nikkhils You will get the same with template1
ivan@bbox:~$ psql "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable" -c 'CREATE DATABASE test_42 WITH template = template1;'
ERROR: source database "template1" is being accessed by other users
DETAIL: There is 1 other session using the database.
ivan@bbox:~$
Also, -c timescaledb.max_background_workers=0
make it work (please see workarounds section). So it's not the postgres issue, it's related to timescaledb workers.
I've seen this same thing in our test system once we added timescaledb into the postgresql.conf configuration (postgres 14). It's included in the shared_preload_libraries but not enabled on the database yet. We drop/restore databases in the test harness to get a clean environment, and sometimes get the same crash, and nothing seems to prevent it but the timescaledb.max_background_workers=0
setting.
Just to chime in that this is still occurring in 2023 (latest docker images), and for our test environment the only mode of configuration available is setting environment variables, however the timescaledb-ha
docker image doesn't support those environment variables :crying_cat_face: (timescale/timescaledb-docker-ha#366)
Hello @devanubis,
Thanks for reaching out. I was also able to reproduce this behavior with the most recent timescale/timescaledb:2.10.0-pg14
container image.
$ docker run -p 5432:5432 -e POSTGRES_PASSWORD=postgres --name timescaledb --rm -d timescale/timescaledb:2.10.0-pg14
$ docker exec -it timescaledb psql -U postgres
postgres=# CREATE DATABASE test_42 WITH template = template1;
ERROR: source database "template1" is being accessed by other users
DETAIL: There is 1 other session using the database.
postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# \dx
List of installed extensions
Name | Version | Schema | Description
-------------+---------+------------+-------------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
timescaledb | 2.10.0 | public | Enables scalable inserts and complex queries for time-series data
(2 rows)
Running into similar issue. I'm creating a fresh database when running tests from the tempalte1
template. Often I get the error about the database being in use by someone else. I checked and it's the timescale db background worker.
Anybody find a workaround? I'm running into this on DigitalOcean's managed PostgreSQL, where I don't even have superuser to try and kill it.
Edit: The workaround I went with was doing SELECT timescaledb_pre_restore();
in the template database, then SELECT timescaledb_post_restore();
in the newly created database.
What type of bug is this?
Locking issue
What subsystems and features are affected?
Background worker
What happened?
I tried to create a database from the template, and instead of that I'm getting an error:
Command that gets an error is:
psql "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable" -c 'CREATE DATABASE test_42 WITH template = postgres;'
Notes
Workaround 1
It is possible to make it work via artificial delay in the beginning of the query:
Workaround 2
It works if postgres started with
-c timescaledb.max_background_workers=0
TimescaleDB version affected
2.5.1
PostgreSQL version used
14
What operating system did you use?
Ubuntu 21.04 x64
What installation method did you use?
Docker
What platform did you run on?
Not applicable
Relevant log output and stack trace
How can we reproduce the bug?