Backup and recovery not working #1797

sshamsudheen · 2020-04-03T11:42:21Z

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?

The text was updated successfully, but these errors were encountered:

mfreed · 2020-04-03T11:58:02Z

Your first issue with the error messages is known/harmless; please see: #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 · 2020-04-03T12:10:11Z

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 · 2020-04-03T12:22:59Z

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 · 2020-04-03T12:36:57Z

\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 · 2020-04-04T04:19:26Z

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 · 2020-04-04T09:09:36Z

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 · 2020-04-06T13:37:26Z

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 · 2020-04-06T13:43:44Z

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 · 2020-04-08T15:00:36Z

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

sshamsudheen · 2020-04-08T15:19:09Z

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 · 2020-04-20T09:33:12Z

Any update on this?

sshamsudheen · 2020-04-20T11:59:33Z

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 · 2020-04-24T08:06:00Z

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 · 2020-09-07T11:26:23Z

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

abhisri1997 · 2022-09-20T03:00:56Z

https://docs.timescale.com/latest/using-timescaledb/backup

Hi, Is the issue fixed where you have to restore from the same version?

sshamsudheen · 2022-09-20T05:59:56Z

Hello, Yes its been fixed Regards, Samsudheen S

…

On Tue, Sep 20, 2022 at 6:01 AM Abhinav Srivastava ***@***.***> wrote: https://docs.timescale.com/latest/using-timescaledb/backup Hi Is the issue fixed where you have to restore in a same version. — Reply to this email directly, view it on GitHub <#1797 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAFNO5EJTMVZOMLBNTW53I3V7ESHHANCNFSM4L34DQBQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

abhisri1997 · 2022-09-21T01:59:09Z

Hello, Yes its been fixed Regards, Samsudheen S
…
On Tue, Sep 20, 2022 at 6:01 AM Abhinav Srivastava @.> wrote: https://docs.timescale.com/latest/using-timescaledb/backup Hi Is the issue fixed where you have to restore in a same version. — Reply to this email directly, view it on GitHub <#1797 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAFNO5EJTMVZOMLBNTW53I3V7ESHHANCNFSM4L34DQBQ . You are receiving this because you were mentioned.Message ID: @.>

One of my DB is using 1.7.5 extension of timescale and I was doing a restore on extension 2.8.0.

I am not able to see any data after the restore

jnidzwetzki · 2022-09-22T10:01:22Z

Hello @dev-elixir,

Thank you very much for reaching out. I would like to reproduce this behavior. Is it possible for you to provide the schema you used, some sample data, and the steps you took to perform the backup and restore?

sshamsudheen · 2022-09-23T06:30:26Z

Hi, I don’t have the schema, it’s been years now since I did this.

…

On Thu 22. Sep 2022 at 12.01, Jan Nidzwetzki ***@***.***> wrote: Hello @dev-elixir <https://github.com/dev-elixir>, Thank you very much for reaching out. I would like to reproduce this behavior. Is it possible for you to provide the schema you used, some sample data, and the steps you took to perform the backup and restore? — Reply to this email directly, view it on GitHub <#1797 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAFNO5CBOPPSNYKVVPYDKVDV7QU73ANCNFSM4L34DQBQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

jnidzwetzki · 2022-09-23T12:45:19Z

Hello @sshamsudheen,

Thank you very much for your reply. Just to make sure I understand the situation correctly: the problem with your database is completely solved and the problem mentioned by @dev-elixir is a new and independent one?

@dev-elixir Please note that restoring a backup with a different version of TimescaleDB not recommended. Please try to restore your backup with the originally used version of TimescaleDB and then upgrade to the latest version of TimescaleDB as described here. If you encounter any problems, please do not hesitate to contact us.

github-actions · 2022-11-23T02:06:26Z

Dear Author,

This issue has been automatically marked as stale due to lack of activity. With only the issue description that is currently provided, we do not have enough information to take action. If you have or find the answers we would need, please reach out. Otherwise, this issue will be closed in 30 days. Thank you!

rkakrik · 2022-12-01T14:43:02Z

I have the same issue. Versions on both servers is 2.8.1.
I made a dump (pg_dump -Fc -f tsdb.bak tsdb) , but it is suspiciously small (242M for 8 million rows with jsonb data).
There are 1363849 rows with new data on the first server, and only 384325 on the new one.

rkakrik · 2022-12-01T17:04:41Z

I have the same issue. Versions on both servers is 2.8.1. I made a dump (pg_dump -Fc -f tsdb.bak tsdb) , but it is suspiciously small (242M for 8 million rows with jsonb data). There are 1363849 rows with new data on the first server, and only 384325 on the new one.

Sorry, my fault - I looked at the logs inattentively, it turns out there was not enough disk space.

mkindahl · 2022-12-02T09:46:43Z

Hi @rkakrik! I will close the issue since it seems there are no actions for us to do here. Please re-open if I misunderstood the situation.

bboule added Backup bug labels Apr 7, 2020

sshamsudheen closed this as completed Apr 20, 2020

jfjoly mentioned this issue Apr 27, 2022

[Site Improvement] Issue with the page: /timescaledb/latest/how-to-guides/backup-and-restore/pg-dump-and-restore/ timescale/docs#1040

Closed

jnidzwetzki added the backup-and-restore label Sep 22, 2022

jnidzwetzki added the waiting-for-author label Sep 22, 2022

github-actions bot removed the waiting-for-author label Sep 23, 2022

jnidzwetzki reopened this Sep 23, 2022

jnidzwetzki added the waiting-for-author label Sep 23, 2022

github-actions bot added the no-activity label Nov 23, 2022

github-actions bot removed waiting-for-author no-activity labels Dec 1, 2022

mkindahl closed this as completed Dec 2, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Backup and recovery not working #1797

Backup and recovery not working #1797

sshamsudheen commented Apr 3, 2020 •

edited

Loading

mfreed commented Apr 3, 2020

sshamsudheen commented Apr 3, 2020

mfreed commented Apr 3, 2020 •

edited

Loading

sshamsudheen commented Apr 3, 2020 •

edited

Loading

mfreed commented Apr 4, 2020

sshamsudheen commented Apr 4, 2020 •

edited

Loading

davidkohn88 commented Apr 6, 2020

sshamsudheen commented Apr 6, 2020 •

edited

Loading

davidkohn88 commented Apr 8, 2020

sshamsudheen commented Apr 8, 2020 •

edited

Loading

sshamsudheen commented Apr 20, 2020

sshamsudheen commented Apr 20, 2020 •

edited

Loading

sshamsudheen commented Apr 24, 2020 •

edited

Loading

longhome87 commented Sep 7, 2020

abhisri1997 commented Sep 20, 2022 •

edited

Loading

sshamsudheen commented Sep 20, 2022 via email

abhisri1997 commented Sep 21, 2022

jnidzwetzki commented Sep 22, 2022

sshamsudheen commented Sep 23, 2022 via email

jnidzwetzki commented Sep 23, 2022

github-actions bot commented Nov 23, 2022

rkakrik commented Dec 1, 2022

rkakrik commented Dec 1, 2022

mkindahl commented Dec 2, 2022

Backup and recovery not working #1797

Backup and recovery not working #1797

Comments

sshamsudheen commented Apr 3, 2020 • edited Loading

mfreed commented Apr 3, 2020

sshamsudheen commented Apr 3, 2020

mfreed commented Apr 3, 2020 • edited Loading

sshamsudheen commented Apr 3, 2020 • edited Loading

mfreed commented Apr 4, 2020

sshamsudheen commented Apr 4, 2020 • edited Loading

davidkohn88 commented Apr 6, 2020

sshamsudheen commented Apr 6, 2020 • edited Loading

davidkohn88 commented Apr 8, 2020

sshamsudheen commented Apr 8, 2020 • edited Loading

sshamsudheen commented Apr 20, 2020

sshamsudheen commented Apr 20, 2020 • edited Loading

sshamsudheen commented Apr 24, 2020 • edited Loading

longhome87 commented Sep 7, 2020

abhisri1997 commented Sep 20, 2022 • edited Loading

sshamsudheen commented Sep 20, 2022 via email

abhisri1997 commented Sep 21, 2022

jnidzwetzki commented Sep 22, 2022

sshamsudheen commented Sep 23, 2022 via email

jnidzwetzki commented Sep 23, 2022

github-actions bot commented Nov 23, 2022

rkakrik commented Dec 1, 2022

rkakrik commented Dec 1, 2022

mkindahl commented Dec 2, 2022

sshamsudheen commented Apr 3, 2020 •

edited

Loading

mfreed commented Apr 3, 2020 •

edited

Loading

sshamsudheen commented Apr 3, 2020 •

edited

Loading

sshamsudheen commented Apr 4, 2020 •

edited

Loading

sshamsudheen commented Apr 6, 2020 •

edited

Loading

sshamsudheen commented Apr 8, 2020 •

edited

Loading

sshamsudheen commented Apr 20, 2020 •

edited

Loading

sshamsudheen commented Apr 24, 2020 •

edited

Loading

abhisri1997 commented Sep 20, 2022 •

edited

Loading