Exporting Data from a 7.2.3 cluster to a 9.0.1 cluster

Hi,

A "Major" release is a ".0" release (i.e. Vertica 8.0, 9.0).

So I believe if you are going to take a full backup then a restore, you will need to first upgrade the 7.2.3 DB to at least Vertica 9.0.

Thanks Jim , but I 'm not talking about an upgrade, we have to migrate/copy physically the data from a 7.2.3 cluster to a 9.0.1 cluster.
The customer is using that opportunity to renew the hardware

I know But if you plan on using vbr to backup and restore, you will have to upgrade the 7.2.3 DB, right?

Or, if you are only migrating user data, I think the COPY FROM VERTICA command might work.

See:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AdministratorsGuide/CopyExportData/CopyingData.htm

The 7.2.x doc for "COPY FROM VERTICA" says this:

You can import data from an earlier Vertica release, as long as the earlier release is a version of the last major release. For instance, for version 7.x, you can import data from any version of 6.x.

See:
https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/COPYFROMVERTICA.htm

But the 9.0.x doc for "COPY FROM VERTICA" doesn't seem to have that limitation. It says:

Imports data from another Vertica database. COPY FROM VERTICA is similar to COPY, but accepts only a subset of its parameters.

See:
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/COPYFROMVERTICA.htm

Well with databases (in general) , I have always preferred the fresh install and data reload method , which is maybe not preferred method with Vertica ?
This is why I was not really considering reinstalling a 7.2.3 installation and then use vbr to duplicate the database, and then upgrade

Back to the doc , the 9.0 doc says clearly :
You can import a table or specific columns in a table from another Vertica database. The table receiving the copied data must already exist, and have columns that match (or can be coerced into) the data types of the columns you are copying from the other database. You can import data from an earlier Vertica release, if the earlier release is a version of the last major release before the target database release.

Hence my question

Fyi... I tested the COPY FROM VERTICA feature to copy data from a 7.2.3 DB to a 9.0.1 DB:

dbadmin=> select version();
              version
------------------------------------
 Vertica Analytic Database v9.0.1-5
(1 row)

dbadmin=> create table test_copy_from_vertica_723 (c1 int, c2 varchar(100));
CREATE TABLE

dbadmin=> CONNECT TO VERTICA vertica723 USER dbadmin PASSWORD 'vertica8' ON '192.168.2.210',5433;
CONNECT

dbadmin=> COPY test_copy_from_vertica_723 FROM VERTICA vertica723.test_copy_from_vertica_723 DIRECT;
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> SELECT * FROM test_copy_from_vertica_723;
 c1 |  c2
----+-------
  1 | TEST1
  2 | TEST2
(2 rows)

dbadmin=> commit;
COMMIT

dbadmin=> create table db_version (c1 varchar(100));
CREATE TABLE

dbadmin=> COPY db_version FROM VERTICA vertica723.vs_global_settings_db DIRECT;
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> select * from db_version;
    c1
-----------
 v7.2.3-26
(1 row)

dbadmin=> DISCONNECT vertica723;
DISCONNECT

Note that the view vs_global_settings_db copied above is defined on the remote server as:

dbadmin=> create view vs_global_settings_db as select dbcreateversion from vs_global_settings;
CREATE VIEW

I wanted to prove that the remote sever is indeed running Vertica 7.2.3.

Thanks for testing Jim
I was pretty sure that was working , after investigation with the support team , it seems they haven't tested import/export from 7.2.3 to 9.0, hence why this is not "supported" (but doesn't mean it is not working...)