Troubleshooting
Troubleshoot common issues with Managed Service for TimescaleDB
Permission denied when changing ownership of tables and hypertables
Message:
ERROR: permission denied for schema _timescaledb_internalYou might see this error when using the ALTER TABLE command to change the
ownership of tables or hypertables.
This use of ALTER TABLE is blocked because the tsdbadmin user is not a
superuser.
To change table ownership, use the REASSIGN command instead:
REASSIGN OWNED BY <current_role> TO <desired_role>;Corrupted unique index has duplicated rows
Message:
ERROR: could not create unique indexDETAIL: Table contains duplicated values.When you try to rebuild an index with REINDEX it fails because of conflicting
duplicated rows.
To identify conflicting duplicate rows, you need to run a query that counts the number of rows for each combination of columns included in the index definition.
For example, this route table has a unique_route_index index defining
unique rows based on the combination of the source and destination columns:
CREATE TABLE route( source TEXT, destination TEXT, description TEXT );
CREATE UNIQUE INDEX unique_route_index ON route (source, destination);If the unique_route_index is corrupt, you can find duplicated rows in the
route table using this query:
SELECT source, destination, countFROM (SELECT source, destination, COUNT(*) AS count FROM route GROUP BY source, destination) AS fooWHERE count > 1;The query groups the data by the same source and destination fields defined
in the index, and filters any entries with more than one occurrence.
Resolve the problematic entries in the rows by manually deleting or merging the
entries until no duplicates exist. After all duplicate entries are removed, you
can use the REINDEX command to rebuild the index.
Reset password
It happens to us all, you want to log in to MST Console, and the password is somewhere next to your keys, wherever they are.
To reset your password:
- Open MST Console.
- Click
Forgot password. - Enter your email address, then click
Reset password.
A secure reset password link is sent to the email associated with this account. Click the link and update your password.
Service is running low on disk, memory, or CPU
Message:
Your Managed Service for TimescaleDB service, in project "ExampleAccount", is running low onCPU. Running low on CPU affects performance and could affect serviceavailability. Please either optimize your usage pattern or reduce the workload,and consider upgrading to a larger plan to avoid service outage.When your database reaches 90% of your allocated disk, memory, or CPU resources, an automated message with the text above is sent to your email address.
You can resolve this by logging in to your Managed Service for TimescaleDB account and changing your plan to increase your available resources:
- Select the service
From the Managed Service for TimescaleDB Dashboard, select the MST service that you want to increase resources for.
- Change the plan
Click
Service Settings>Service summary>Change Plan. Select the suitable plan and clickChange Plan.
If you run out of resources regularly, you might need to consider using your resources more efficiently. Consider enabling hypercore, using continuous aggregates, or configuring data retention to reduce the amount of resources your database uses.
Manage inactive or lagging replication slots
An inactive or lagging replication slot makes PostgreSQL keep write-ahead log (WAL) files until the slot's consumer reads them. Disk usage then grows without any growth in the data itself. When the disk fills, the MST service stops serving clients.
- Check the replication slots
Connect to the service with
psqland run:SELECT slot_name, restart_lsn FROM pg_replication_slots;The output looks like this:
slot_name │ restart_lsn──────────────┼─────────────pghoard_local │ 6E/16000000dest_slot | 5B/8B0(2 rows) - Compare the slot with
pghoard_localpghoard_localis the slot the platform's own backups use. The hexadecimal difference between itsrestart_lsnand the slot you are checking is the number of WAL entries that slot still has to consume. In the example,0x6E - 0x5Bis 19 entries behind. - Restart or drop the lagging slot
-
If the consumer is still in use, restart it. Disable and re-enable its subscription with
aiven_extras:SELECT * FROM aiven_extras.pg_alter_subscription_disable('<SUBSCRIPTION_NAME>');SELECT * FROM aiven_extras.pg_alter_subscription_enable('<SUBSCRIPTION_NAME>'); -
If the consumer is no longer needed, drop the slot:
SELECT pg_drop_replication_slot('<SLOT_NAME>');
-
In both cases the reserved WAL space is freed at the next PostgreSQL checkpoint.
A checkpoint runs when 15 minutes have elapsed (checkpoint_timeout is 900
seconds) or when WAL reaches max_wal_size, which is set to 5% of the
service's storage.
Replication slots are recreated automatically after a maintenance update or a failover, but not after a major version upgrade.
For more about WAL and checkpoints, see the PostgreSQL documentation.
Problem resolving DNS
Managed Service for TimescaleDB services require a DNS record. When you launch a new MST service the DNS record is created, and it can take some time for the new name to propagate to DNS servers around the world.
If you move an existing service to a new Cloud provider or region, the MST service is rebuilt in the new region in the background. When the MST service has been rebuilt in the new region, the DNS records are updated. This could cause a short interruption to your MST service while the DNS changes are propagated.
If you are unable to resolve DNS, wait a few minutes and try again.
PostgreSQL transaction ID wraparound
The transaction control mechanism in PostgreSQL assigns a transaction ID to
every row that is modified in the database; these IDs control the visibility of
that row to other concurrent transactions. The transaction ID is a 32-bit number
where two billion IDs are always in the visible past and the remaining IDs are
reserved for future transactions and are not visible to the running transaction.
To avoid a transaction wraparound of old rows, PostgreSQL requires occasional
cleanup and freezing of old rows. This ensures that existing rows are visible
when more transactions are created. You can manually freeze the old rows by
executing VACUUM FREEZE. It can also be done automatically using the
autovacuum daemon when a configured number of transactions has been created
since the last freeze point.
In Managed Service for TimescaleDB, the transaction limit is set according to
the size of the database, up to 1.5 billion transactions. This ensures 500
million transaction IDs are available before a forced freeze and avoids
churning stable data in existing tables. To check your transaction freeze
limits, you can execute show autovacuum_freeze_max_age in your PostgreSQL
instance. When the limit is reached, autovacuum starts freezing the old rows.
Some applications do not automatically adjust the configuration when the PostgreSQL
settings change, which can result in unnecessary warnings. For example,
PGHero's default settings alert when 500 million transactions have been created
instead of alerting after 1.5 billion transactions. To avoid this, change the
value of the transaction_id_danger setting from 1,500,000,000 to
500,000,000, to receive warnings when the transaction limit reaches 1.5 billion.