Обсуждение: How to import PostgreSQL 9.2.4 dump to PostgreSQL 9.4.5?
Hi guys, I'm running some test. However, I'm stuck in restoring a PostgreSQL 9.2.4 dump to PostgreSQL 9.4.5 database. I'm doing the backup using pg_dump version PostgreSQL 9.2.4 in text file (SQL dump). I'm trying to restore it to PostgreSQL 9.4.5 usng psql. The restoration error message was as below:psql:room.sql:41695: invalid command \n Query buffer reset (cleared). psql:room.sql:41696: invalid command \n Query buffer reset (cleared). The SQL statement in the file as below: 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 14295286497985561429528649798556 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 14267677738225381426767773822538 closed 1426754122000000 0 1426754122000000 2 0 1 \N 0 550a8a4a73656d2e26160000 2015-09-12 14:05:32 2015-09-12 14:05:32 I create the room table as below: CREATE TABLE room ( id integer NOT NULL, user_id character varying(255), partner_id character varying(255), rm_cr_tsbigint, rm_up_ts bigint, rm_sv_ts bigint, value character varying(255), last_dlvrd_to_user bigint, last_dlvrd_to_prtnrbigint, last_seen_by_user bigint, last_seen_by_prtnr bigint, num_unseen_by_user integer, num_unseen_by_prtnrinteger, last_message_id integer, last_message_ts bigint, legacy_id text, legacy_last_message_idtext, inserted_at timestamp without time zone NOT NULL, updated_at timestamp without time zoneNOT NULL ); All the servers are Debian 7.8 (Wheezy) with hardware: - CPU 24 cores - RAM 128GB - SSD 128GB Have anyone try this before? How to do it properly? Thanks in advance, Fattah
Hi
2015-10-15 11:27 GMT+02:00 FattahRozzaq <ssoorruu@gmail.com>:
Hi guys,
I'm running some test.
However, I'm stuck in restoring a PostgreSQL 9.2.4 dump to PostgreSQL
9.4.5 database.
I'm doing the backup using pg_dump version PostgreSQL 9.2.4 in text
file (SQL dump).
I'm trying to restore it to PostgreSQL 9.4.5 usng psql.
try to use 9.4.x pg_dump
Regards
Pavel
The restoration error message was as below:
psql:room.sql:41695: invalid command \n
Query buffer reset (cleared).
psql:room.sql:41696: invalid command \n
Query buffer reset (cleared).
The SQL statement in the file as below:
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
I create the room table as below:
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
);
All the servers are Debian 7.8 (Wheezy) with hardware:
- CPU 24 cores
- RAM 128GB
- SSD 128GB
Have anyone try this before?
How to do it properly?
Thanks in advance,
Fattah
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Oct 15, 2015 at 11:27 AM, FattahRozzaq <ssoorruu@gmail.com> wrote:
Hi guys,
I'm running some test.
However, I'm stuck in restoring a PostgreSQL 9.2.4 dump to PostgreSQL
9.4.5 database.
I'm doing the backup using pg_dump version PostgreSQL 9.2.4 in text
file (SQL dump).
I'm trying to restore it to PostgreSQL 9.4.5 usng psql.
The restoration error message was as below:
psql:room.sql:41695: invalid command \n
Query buffer reset (cleared).
psql:room.sql:41696: invalid command \n
Query buffer reset (cleared).
The SQL statement in the file as below:
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
Not sure how that could happen, but this second line has only 18 fields instead of the expected 19. The first one appears to be correct.
I create the room table as below:
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
);
All the servers are Debian 7.8 (Wheezy) with hardware:
- CPU 24 cores
- RAM 128GB
- SSD 128GB
Have anyone try this before?
How to do it properly?