Hi dear,
I currently test to import some PostgreSQL vanilla 9.2.4 dump using psql.
The PostgreSQL 9.2.4 vanilla dump was made by pg_dump.
The exception messages are as below"
psql:/home/postgres/backup/20151013-150001/room.dump:206864: invalid command \N
Below is the command that:
COPY room (id, user_id, partner_id, rm_cr_ts, rm_up_ts, rm_sv_ts, value, last_dlvrd_to_user, last_dlvrd_to_prtnr, last_seen_by_user, last_seen_by_prtnr, num_unseen_by_user, num_unseen_by_prtnr, last_message_id, last_message_ts, legacy_id, legacy_last_message_id, inserted_at, updated_at) FROM stdin;
711675 1391103 2657167 -1429528649798556 1429528649798556 1429528649798556 created 0 0 0 0 1 0 19733451 1429528649798556 AUzWjCFTbRHQVpj5SYnD AUzWjCR43gMouVUpyttw 2015-09-12 18:56:49 2015-09-12 18:56:49
19 40868 1191438 1426767773822538 1426767773822538 1426767773822538 closed 1426754122000000 0 1426754122000000 2 0 1 \N 0 550a8a4a73656d2e26160000 2015-09-12 14:05:32 2015-09-12 14:05:32
Below is the command I ran to create the table and distribute it:
CREATE TABLE room (
id integer NOT NULL,
user_id character varying(255),
partner_id character varying(255),
rm_cr_ts bigint,
rm_up_ts bigint,
rm_sv_ts bigint,
value character varying(255),
last_dlvrd_to_user bigint,
last_dlvrd_to_prtnr bigint,
last_seen_by_user bigint,
last_seen_by_prtnr bigint,
num_unseen_by_user integer,
num_unseen_by_prtnr integer,
last_message_id integer,
last_message_ts bigint,
legacy_id text,
legacy_last_message_id text,
inserted_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL
);
SELECT master_create_distributed_table(table_name := 'room', partition_column := 'id');
SELECT master_create_worker_shards(table_name := 'room', shard_count := 16, replication_factor := 2);
I use 3 servers with all the same specifications as below"
PostgreSQL version: 9.4.5
OS: Debian GNU/Linux 7.8 (wheezy)
RAM: 128GB
CPU: 24 cores
SSD: 128GB
So, can we pg_dump data from PostgreSQL 9.2.4 vanilla to PostgreSQL 9.4.5+pg_shard?
How to properly do it?
Thanks in advance,
Fattah