CODEWITHSUNDEEP
postgresqlpg-dumptimescaledb
beatrice
beatrice

Reputation: 4401

Is it possible to dump from Timescale without hypertable insertions?

I followed the manual on: https://docs.timescale.com/v1.0/using-timescaledb/backup

When I dump it into a binary file everything work out as expected (can restore it easily).

However, when I dump it into plain text SQL, insertions to hyper tables will be created. Is that possible to create INSERTION to the table itself?

Say I have an 'Auto' table with columns of id,brand,speed and with only one row: 1,Opel,170

dumping into SQL will result like this:

INSERT INTO _timescaledb_catalog.hypertable VALUES ...
INSERT INTO _timescaledb_internal._hyper_382_8930_chunk VALUES (1, 'Opel',170);

What I need is this (and let TS do the work in the background):

INSERT INTO Auto VALUES (1,'Opel',170);

Is that possible somehow? (I know I can exclude tables from pg_dump but that wouldn't create the needed insertion)

Upvotes: 3

Views: 2715

Answers (2)

Umer Farooq
Umer Farooq

Reputation: 1

you can use the child_process module in Node.js to run shell commands. Here's a simple script that runs the pg_dump command:

const { exec } = require('child_process');

const user = 'user';
const host = 'host';
const port = '5433';
const database = 'dbname';
const filename = 'dbname.dump';

const command = `pg_dump -U ${user} -h ${host} -p ${port} -d ${database} -F c -b -v -f "${filename}"`;

exec(command, (error, stdout, stderr) => {
    if (error) {
        console.error(`Error: ${error.message}`);
        return;
    }

    if (stderr) {
        console.error(`Stderr: ${stderr}`);
        return;
    }

    console.log(`Stdout: ${stdout}`);
});

This script constructs the pg_dump command with the provided parameters and then runs it using exec. The output (or any errors) of the command are logged to the console.

Upvotes: -1

Amy T
Amy T

Reputation: 19

Beatrice. Unfortunately, pg_dump will dump commands that mirror the underlying implementation of Timescale. For example, _hyper_382_8930_chunk is a chunk underlying the auto hypertable that you have.

Might I ask why you don't want pg_dump to behave this way? The SQL file that Postgres creates on a dump is intended to be used by pg_restore. So as long as you dump and restore and see correct state, there is no problem with dump/restore.

Perhaps you are asking a different question?

Upvotes: 1

Related Questions