Timescaledb: Backup and recovery not working

[sshamsudheen]

System Information:

OS: Linux ip-10-79-3-192 4.15.0-1063-aws #67-Ubuntu SMP Mon Mar 2 07:24:29 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
Psql version: psql (PostgreSQL) 11.7 (Ubuntu 11.7-2.pgdg18.04+1)
Timescale version: 1.6.1

I followed the link (https://docs.timescale.com/latest/using-timescaledb/backup) to recover the DB from backing up the timescaledb.

I ended up many notice and at the end the db is not recovered fully.

pg_dump gave me some notice, after going through various discussion i found the below notices can be ignored

pg_dump -h localhost -U username -p 5434 -Fc -f old_db.back old_db

pg_dump: NOTICE: there are circular foreign-key constraints on this table:
pg_dump:   hypertable
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
pg_dump: NOTICE: there are circular foreign-key constraints on this table:
pg_dump:   chunk
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
pg_dump: NOTICE:  hypertable data are in the chunks, no data will be copied
DETAIL:  Data for hypertables are stored in the chunks of a hypertable so COPY TO of a hypertable will not copy any data.
HINT:  Use "COPY (SELECT * FROM <hypertable>) TO ..." to copy all data in hypertable, or copy each chunk individually.

executed below command to restore

psql -h localhost -U username -p 5434 -d postgres -c 'create database "new_db"'
psql -h localhost -U username -p 5434 -d new_db -c 'CREATE EXTENSION timescaledb'
psql -h localhost -U username -p 5434 -d new_db -c 'SELECT timescaledb_pre_restore();'

pg_restore -h localhost -p 5434 -U username -Fc -d new_db old_db.bak

psql -h localhost -U username -p 5434 -d new_db -c 'SELECT timescaledb_post_restore();

After this i found that the Access privilegs for the new database is missing and hyper table information returns the values which is less comparing to old_db

=>  SELECT * FROM timescaledb_information.hypertable;
 table_schema |          table_name          | table_owner | num_dimensions | num_chunks | table_size | index_size | toast_size | total_size
--------------+------------------------------+-------------+----------------+------------+------------+------------+------------+------------
 some_schema  | table_name            | adminuser   |              1 |         72 | 9392 kB   | 7672 kB     | 576 kB     | 14 MB

new_db
index_size | toast_size | total_size
--------------+------------------------------+-------------+----------------+------------+------------+------------+------------+------------
 some_schema  | table_name            | adminuser   |              1 |         72 | 7656 kB    | 6264 kB    | 576 kB     | 17 MB

also selecting chunks from new db gives the below error where it works fine in old_db

=> SELECT chunk_id,chunk_table,partitioning_columns,partitioning_column_types,ranges,table_size,index_size,toast_size,total_size FROM chunk_relation_size_pretty('hypertablename');
ERROR:  function chunk_relation_size_pretty(unknown) does not exist
LINE 1: ...,table_size,index_size,toast_size,total_size FROM chunk_rela...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

may i know where am i making mistake.. or do i missing anything?

[mfreed]

Your first issue with the error messages is known/harmless; please see: https://github.com/timescale/timescaledb/issues/1581

For the second issue, in slack you mentioned that your backup was 1.5.1 and the new DB was 1.6.1, is that still the case?

You need to restore into the same version of the extension as the backup: so restore into 1.5.1, then upgrade the restored DB from 1.5.1 to 1.6.1. (We are working on a tool to overcome this limitation.)

[sshamsudheen]

i already upgraded the extension of my old_db to 1.6.1, then i took the backup and restoring in to new DB (which too has the version 1.6.1 installed)

[mfreed]

Can you do a \dx timescaledb on the new database? The error of "no function found" suggests to me that the extension isn't installed or loaded somehow.

Also, what happens when you just SELECT count(*) FROM hypertable on old and new? I can imagine scenarios where deleted data hasn't yet been removed/vacuumed from a old db so the MVCC can lead to differences in reported sizes, or some other old state.

[sshamsudheen]

\dx timescaledb
                                    List of installed extensions
    Name     | Version | Schema |                            Description
-------------+---------+--------+-------------------------------------------------------------------
 timescaledb | 1.6.1   | public | Enables scalable inserts and complex queries for time-series data

do you mean SELECT count(*) FROM timescaledb_information.hypertable;?
if so hypertable count in old and new db is same

=> SELECT count(*) FROM timescaledb_information.hypertable;
 count
-------
     5
(1 row)
                             ^

[mfreed]

No, I was more curious about the row count in each of your hypertables, to better understand if data was somehow not copied.

Are you continuing to see the function does not exist error, possibly after restarting the DB?

[sshamsudheen]

row counts are same in both the DB.. however the size of old DB and new DB is differing

size of old DB is below

=> SELECT * FROM timescaledb_information.hypertable;
 table_schema |          table_name          | table_owner | num_dimensions | num_chunks | table_size | index_size | toast_size | total_size
--------------+------------------------------+-------------+----------------+------------+------------+------------+------------+------------
 schema  | table2     | adminuser   |              1 |        156 | 7752 kB    | 8472 kB    |            | 16 MB
 schema  | table1            | adminuser   |              1 |         72 | 9392 kB    | 7672 kB    | 576 kB     | 17 MB
 schema  | table3   | adminuser   |              1 |        132 | 21 MB      | 11 MB      | 1056 kB    | 34 MB
 schema  | table4  | adminuser   |              1 |        130 | 5736 kB    | 6408 kB    |            | 12 MB
 schema  | table5 | adminuser   |              1 |        101 | 90 MB      | 48 MB      | 808 kB     | 138 MB

where as the size of new db is

=>  SELECT * FROM timescaledb_information.hypertable;
 table_schema |          table_name          | table_owner | num_dimensions | num_chunks | table_size | index_size | toast_size | total_size
--------------+------------------------------+-------------+----------------+------------+------------+------------+------------+------------
 schema  | table1            | adminuser   |              1 |         72 | 7656 kB    | 6264 kB    | 576 kB     | 14 MB
 schema  | table | adminuser   |              1 |        101 | 75 MB      | 29 MB      | 808 kB     | 105 MB
 schema  | table3   | adminuser   |              1 |        132 | 18 MB      | 9296 kB    | 1056 kB    | 29 MB
 schema  | table2     | adminuser   |              1 |        156 | 6920 kB    | 7088 kB    |            | 14 MB
 schema  | table4  | adminuser   |              1 |        130 | 4976 kB    | 5328 kB    |            | 10 MB

yes i am seeing the below error even after restarting the DB

=> SELECT chunk_id,chunk_table,partitioning_columns,partitioning_column_types,ranges,table_size,index_size,toast_size,total_size FROM chunk_relation_size_pretty('schema.table1');
ERROR:  function chunk_relation_size_pretty(unknown) does not exist
LINE 1: ...,table_size,index_size,toast_size,total_size FROM chunk_rela...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

[davidkohn88]

The size of the db differing is not that surprising you have likely eliminated some table bloat.
Do selects from the hypertable work? Inserts?
and does the function exist?

[sshamsudheen]

Yes i found the total number of records are same. however some of the indexes are missing in all the tables.

example below

Old DB: table

                                    Table "schema.table_name"
         Column          |            Type             | Collation | Nullable |           Default
-------------------------+-----------------------------+-----------+----------+------------------------------
 timestamp               | timestamp without time zone |           | not null |
 time_series_identifier  | uuid                        |           | not null |
 reported_at             | timestamp without time zone |           |          |
 received_at             | timestamp without time zone |           | not null | timezone('utc'::text, now())
Indexes:
    "pk_XXXXXXX" PRIMARY KEY, btree ("timestamp", time_series_identifier)
    "idx_XXXXX" btree (time_series_identifier, "timestamp")
    "XXXXX_idx" btree ("timestamp" DESC)

where as in the new DB

                                    Table "schema.table_name"
         Column          |            Type             | Collation | Nullable |           Default
-------------------------+-----------------------------+-----------+----------+------------------------------
 timestamp               | timestamp without time zone |           | not null |
 time_series_identifier  | uuid                        |           | not null |

 reported_at             | timestamp without time zone |           |          |
 received_at             | timestamp without time zone |           | not null | timezone('utc'::text, now())
Indexes:
    "XXXXX_idx" btree ("timestamp" DESC)
Check constraints:

seems dump fail to fetch all the index (postgress issue)

[davidkohn88]

Did you get any output about that during pg_restore or pg_dump?

[sshamsudheen]

some error while pg_restore

psql:dump.sql:681392: ERROR: operation not supported on chunk tables

the corresponding line in sql file is

--
-- Name: measurement_value_10_chunk 10_10_pk_measurement_measurement_value; Type: CONSTRAINT; Schema: measurement; Owner: adminuser
--

ALTER TABLE ONLY measurement.measurement_value_10_chunk
    ADD CONSTRAINT "10_10_pk_measurement_measurement_value" PRIMARY KEY ("timestamp", time_series_identifier);

this states that constraints are not restored..

Note: i have multiple db running in my db instance and i am not restoring all the database, i just wanted to restore one DB

[sshamsudheen]

Any update on this?

[sshamsudheen]

I noticed while re-storing the extensions, it is storing the function name with schema

Old DB has following extensions

 function time_bucket_gapfill(smallint,smallint,smallint,smallint)
 function timescaledb_post_restore()
 function timescaledb_pre_restore()

where as New DB has the schema before the function name

function measurement.time_bucket_gapfill(smallint,smallint,smallint,smallint)
 function measurement.timescaledb_post_restore()
 function measurement.timescaledb_pre_restore()

How can we get rid of the schema name while restoring or is there any work around

[sshamsudheen]

i found what the issue was.. all the TSDB functions are restoring under different schema..

to restore all the functions to particular schema, i used the below commands

CREATE DATABASE tutorial;
\c tutorial --connect to the db where we'll perform the restore

CREATE SCHEMA your_schema_name;

CREATE EXTENSION IF NOT EXISTS timescaledb WITH SCHEMA your_schema_name;

SELECT timescaledb_pre_restore();

\! pg_restore -Fc tutorial.bak -C > tutorial.sql

psql -h localhost -U uername -p 5432 -d tutorial < tutorial.sql

#the above command helped me in restoring all the indexes, privileges, triggers and constraints

SELECT timescaledb_post_restore();

[longhome87]

Thank you very much @sshamsudheen This help me to solve my case.