0

Simple question: I want to migrate all of a Postgres server's data, schema, roles, everything from Windows to Linux.

Can I just copy the contents of the %PGDATA% directory in Windows to which the server points to Linux and start the server there pointing PGDATA argument/variable to that directory? In other words, are the data files platform independent to make porting from one OS to another a matter of copying?

I also have developed a script for cloning PG databases, schema+data but, for a variety of reasons, I would prefer to just put the data files on a USB drive and be done with it.

0
4

No, you cannot do that. The files in the data directory are not compatible across operating systems (or different architectures).

You will need to use pg_dump and pg_restore

1
  • 2
    You may also need to care about encoding or collation for some languages. – Sahap Asci Nov 15 '18 at 6:12
0

Just for the record, you should ask in PostgreSQL mailing list.

My personal idea is the binary format is fixed, so no problem.

Anyway, just to try, I:

a) copy the pgdata directory, from PG9.5 from Windows Server 2012 to Linux;

b) compile 9.5 on Linux;

c) change the specific Windows options in postgresql.conf;

d) run it...

And it starts, but it complains about locale (I have to investigate further to figure out):

LOG: database system was interrupted; last known up at 2020-02-27 03:57:27 CET

LOG: database system was not properly shut down; automatic recovery in progress

LOG: redo starts at 34/DFD49EF8

LOG: invalid record length at 34/DFE33560

LOG: redo done at 34/DFE33538

LOG: last completed transaction was at log time 2020-02-27 04:00:52.844739+01

LOG: MultiXact member wraparound protections are now enabled

LOG: database system is ready to accept connections

LOG: autovacuum launcher started

FATAL: no pg_hba.conf entry for host "[local]", user "gelma", database "postgres"

FATAL: no pg_hba.conf entry for host "[local]", user "gelma", database "postgres"

FATAL: no pg_hba.conf entry for host "[local]", user "gelma", database "template1"

FATAL: no pg_hba.conf entry for host "[local]", user "gelma", database "template0"

FATAL: role "gelma" does not exist

FATAL: database locale is incompatible with operating system

DETAIL: The database was initialized with LC_COLLATE "Italian_Italy.1252", which is not recognized by setlocale().

HINT: Recreate the database with another locale or install the missing locale.

FATAL: database locale is incompatible with operating system

DETAIL: The database was initialized with LC_COLLATE "Italian_Italy.1252", which is not recognized by setlocale().

Anyway the server is up and running. Also using pg_dump I don't think the locale is automagically converted.

So, finding the original super user and how to change collate/locale should be the only things left.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.