I need to load the data in psql db time-to-time via a node app.
I'm using node-postgres package, which seems to be working fine with INSERT statements.
Since my db_dum is huge, I need to move to COPY statement in pg_dump (for better performance), but getting all kinds of error while trying to load the data with pg package in Node - This works find if I use command line psql
the psql dump file I have is huge which includes COPY statements like this:
COPY hibernate.address (id, create_timestamp,
update_timestamp, street_address_line_1, street_address_line_2, city, state_code, postal_code, postal_code_suffix, country_code, latitude, longitude, updated_by_base_user_id) FROM stdin;
379173 2017-02-20 02:34:17.715-08 2018-01-20 08:34:17.715-08 3 Brewster St \N Phoenix AZ 17349 \N US \N \N 719826
/.
Here's a pseudo code for the node app running the sql dump file:
const sqlFile = fs.readFileSync('data_dump.sql').toString();
const connectionString = `postgres://<user>:${PgPassword}@${pgIpAndPort}/<table>`;
const client = new pg.Client(connectionString);
lient.connect();
client.query(sqlFile);
Here's a sample pg_dump command I use (which is for data-only - no schema):
pg_dump -U <user> --data-only --disable-triggers -n hibernate <table_name> > <dump_file.sql>
but it doesn't work when I'm trying to load the data via node app
I know --column-inserts would solve the problem, but that decreases the performance drastically.
So I'm looking for possible solutions for loading the data with COPY tbl FROM stdin; statement in the node app
Any suggestion/comments is appreciated.
