pg_upgrade change id database

[marian]

Hi everyone,

i made upgrade postgres from 9.4 to 9.5 via pg_upgrade. Then I moved cstore data to new postgres. pg_upgrade changed oid for databases. I changed directory name in cstore data structure and it works :)

Question: is easy method how to upgrade postges with cstore?

We have more servers with cstore, its great product.
Regards!
marian

[Juan Emilio Gabito Decuadra]

Hi, can you tell how you move data from cstore to the new postgres?

Did you recreate the tables?

And what data structure did you update to change the directory name?

Regards!

[Marian Krucina]

Hi,

1. I ran pg_upgrade for upgrade postgresql

2. I moved directory cstore_fdw from old PGDATA to new PGDATA directory

3. Select fail:could not open file "/home/pgsql_data/data/cstore_fdw/16430/532324492.footer" for reading: No such file or directory

4. pg_upgrade not change oid of tables, so I found file 532324492.footer and rename his directory to 16430

5. It works :)

Upgrade by dump, restore changes oid of tables and I don't known, how to restore cstore data.

Regards

marian

--
You received this message because you are subscribed to a topic in the Google Groups "cstore users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/cstore-users/Ck4GkpppCVQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to cstore-users...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Murat Tuncer]

Hey folks,

If you added or dropped columns, please DO NOT use this method. cstore data file would still contain data for old columns, it could reject the data file when you try to open after restoring.

Again, this method is safe only if you haven't changed anything on table columns.

proved (little bit longer) way  is to pg_dump/copy from old instance, pg_restore/copy in to new instance.

while old instance was running

1- pg_dump -d postgres -t your_cstore_table > your_cstore_table.ddl

2- psql -d postgres -c "copy your_cstore_table to stdout with csv" > your_cstore_table.data

3- copy ddl and data files to new server/instance

4- psql -d postgres -f your_cstore_table.ddl

5- psql -d postgres -c "\copy your_cstore_table from your_cstore_table.data with csv"

 

if you use pg_upgrade you could skip steps 1 and 4.

1. psql -d postgres -c "copy your_cstore_table to stdout with csv" > your_cstore_table.data

2. run pg_upgrade to upgrade the instance

3.  psql -d postgres -c "\copy your_cstore_table from your_cstore_table.data with csv"

please let me know if you need any assistance with this.

Murat

--
You received this message because you are subscribed to the Google Groups "cstore users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cstore-users...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Murat Tuncer
Software Engineer | Citus Data
mtu...@citusdata.com