Postgres restore problem

Support - Assistance technique
1

Hi,

I’m trying this on my server:

pg_restore -i -d dhis2_demo -v dhis2demo.backup

but all I get is this:

pg_restore: [archiver] input file does not appear to be a valid archive

Is this likely to be because I still have 8.4 on the server? If so, any tips on upgrading (I have lots of data on the server)?

Knut

2

Hi

I'm no expert, but... the dhis2demo.backup file is not is a dump
format. It is in plain text.

So you need to do something like this:
psql -d dbname -U username -f dhis2demo.backup

···

--
Morten

On Wed, May 4, 2011 at 1:40 PM, Knut Staring <knutst@gmail.com> wrote:

Hi,
I'm trying this on my server:
pg_restore -i -d dhis2_demo -v dhis2demo.backup
but all I get is this:
pg_restore: [archiver] input file does not appear to be a valid archive
Is this likely to be because I still have 8.4 on the server? If so, any tips
on upgrading (I have lots of data on the server)?
Knut

_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help

3

Yep, got it. Thanks!

Knut

···

On Wed, May 4, 2011 at 1:58 PM, Morten Olav Hansen mortenoh@gmail.com wrote:

Hi

I’m no expert, but… the dhis2demo.backup file is not is a dump

format. It is in plain text.

So you need to do something like this:

psql -d dbname -U username -f dhis2demo.backup

–

Morten

On Wed, May 4, 2011 at 1:40 PM, Knut Staring knutst@gmail.com wrote:

Hi,

I’m trying this on my server:

pg_restore -i -d dhis2_demo -v dhis2demo.backup

but all I get is this:

pg_restore: [archiver] input file does not appear to be a valid archive

Is this likely to be because I still have 8.4 on the server? If so, any tips

on upgrading (I have lots of data on the server)?

Knut

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Cheers,
Knut Staring

4

What is the file format? Is it a plain text Postgres dump or has it been created using pg_dump as a compressed dump?

You are right, as you will not be able to (easily) restore a version of the database created with Postgres 9 to postgres 8.4. You should be able to restore from a plain text dump however.

···

On Wed, May 4, 2011 at 1:40 PM, Knut Staring knutst@gmail.com wrote:

Hi,

I’m trying this on my server:

pg_restore -i -d dhis2_demo -v dhis2demo.backup

but all I get is this:

pg_restore: [archiver] input file does not appear to be a valid archive

Is this likely to be because I still have 8.4 on the server? If so, any tips on upgrading (I have lots of data on the server)?

Knut

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293

5

If dhis2demo.backup is the sample db, it might just be that it is in sql format?

In that case, I guess just run
psql dhis2_demo < dhis2demo.backup

Jo

···

Den 4. mai 2011 kl. 13.40 skrev Knut Staring:

I'm trying this on my server:
pg_restore -i -d dhis2_demo -v dhis2demo.backup

but all I get is this:
pg_restore: [archiver] input file does not appear to be a valid archive

Is this likely to be because I still have 8.4 on the server? If so, any tips on upgrading (I have lots of data on the server)?

6

What is the file format? Is it a plain text Postgres dump or has it been created using pg_dump as a compressed dump?

I am talking about the demo db downloaded from dhis2.org, which has the suffix .backup but maybe should rather be names .sql for clarity - since psql is the right tool to restore it.

Knut

···

On Wed, May 4, 2011 at 2:01 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

You are right, as you will not be able to (easily) restore a version of the database created with Postgres 9 to postgres 8.4. You should be able to restore from a plain text dump however.

On Wed, May 4, 2011 at 1:40 PM, Knut Staring knutst@gmail.com wrote:

Hi,

I’m trying this on my server:

pg_restore -i -d dhis2_demo -v dhis2demo.backup

but all I get is this:

pg_restore: [archiver] input file does not appear to be a valid archive

Is this likely to be because I still have 8.4 on the server? If so, any tips on upgrading (I have lots of data on the server)?

Knut

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293

–
Cheers,
Knut Staring

7

Just above that download link is says:
“The PostgreSQL file must be unzipped and can be imported through pgAdmin restore function or with psql -d dbname -U username -f dhis2demo.backup” :slight_smile:

It is not real sql either, but postgres’s plain text dump format.

Ola

···

On 4 May 2011 14:12, Knut Staring knutst@gmail.com wrote:

On Wed, May 4, 2011 at 2:01 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

What is the file format? Is it a plain text Postgres dump or has it been created using pg_dump as a compressed dump?

I am talking about the demo db downloaded from dhis2.org, which has the suffix .backup but maybe should rather be names .sql for clarity - since psql is the right tool to restore it.

Knut

You are right, as you will not be able to (easily) restore a version of the database created with Postgres 9 to postgres 8.4. You should be able to restore from a plain text dump however.

On Wed, May 4, 2011 at 1:40 PM, Knut Staring knutst@gmail.com wrote:

Hi,

I’m trying this on my server:

pg_restore -i -d dhis2_demo -v dhis2demo.backup

but all I get is this:

pg_restore: [archiver] input file does not appear to be a valid archive

Is this likely to be because I still have 8.4 on the server? If so, any tips on upgrading (I have lots of data on the server)?

Knut

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293

–
Cheers,
Knut Staring

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

8

What is the file format? Is it a plain text Postgres dump or has it been created using pg_dump as a compressed dump?

I am talking about the demo db downloaded from dhis2.org, which has the suffix .backup but maybe should rather be names .sql for clarity - since psql is the right tool to restore it.

Just above that download link is says:
“The PostgreSQL file must be unzipped and can be imported through pgAdmin restore function or with psql -d dbname -U username -f dhis2demo.backup” :slight_smile:

Well - sadly it is not possible to create an idiot proof point&click interface for those of us with such short memory that I didn’t even remember having I asked Lars to correct the filename in that exact command last week…

Must be getting really old…

k

···

On Wed, May 4, 2011 at 2:21 PM, Ola Hodne Titlestad olati@ifi.uio.no wrote:

On 4 May 2011 14:12, Knut Staring knutst@gmail.com wrote:

On Wed, May 4, 2011 at 2:01 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

It is not real sql either, but postgres’s plain text dump format.

Ola

Knut

You are right, as you will not be able to (easily) restore a version of the database created with Postgres 9 to postgres 8.4. You should be able to restore from a plain text dump however.

On Wed, May 4, 2011 at 1:40 PM, Knut Staring knutst@gmail.com wrote:

Hi,

I’m trying this on my server:

pg_restore -i -d dhis2_demo -v dhis2demo.backup

but all I get is this:

pg_restore: [archiver] input file does not appear to be a valid archive

Is this likely to be because I still have 8.4 on the server? If so, any tips on upgrading (I have lots of data on the server)?

Knut

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293

–
Cheers,
Knut Staring

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Cheers,
Knut Staring

9

Just to clear up - pgAdmin can export:

  • BACKUP

  • compressed backup

  • imported by pgAdmin restore, not by the psql command

  • called .backup

  • TAR

  • SQL

  • imported by pgAdmin restore, not by the psql command

  • PLAIN

  • postgres’ plain text dump format like Ola said, the same as using the pg_dump command

  • imported by the psql command, can not be imported by pgAdmin restore

  • should not be called .backup, but .pgdump or .pgplain etc

···

On Wed, May 4, 2011 at 14:33, Knut Staring knutst@gmail.com wrote:

On Wed, May 4, 2011 at 2:21 PM, Ola Hodne Titlestad olati@ifi.uio.no wrote:

On 4 May 2011 14:12, Knut Staring knutst@gmail.com wrote:

On Wed, May 4, 2011 at 2:01 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

What is the file format? Is it a plain text Postgres dump or has it been created using pg_dump as a compressed dump?

I am talking about the demo db downloaded from dhis2.org, which has the suffix .backup but maybe should rather be names .sql for clarity - since psql is the right tool to restore it.

Just above that download link is says:
“The PostgreSQL file must be unzipped and can be imported through pgAdmin restore function or with psql -d dbname -U username -f dhis2demo.backup” :slight_smile:

Well - sadly it is not possible to create an idiot proof point&click interface for those of us with such short memory that I didn’t even remember having I asked Lars to correct the filename in that exact command last week…

Must be getting really old…

k

It is not real sql either, but postgres’s plain text dump format.

Ola

Knut

You are right, as you will not be able to (easily) restore a version of the database created with Postgres 9 to postgres 8.4. You should be able to restore from a plain text dump however.

On Wed, May 4, 2011 at 1:40 PM, Knut Staring knutst@gmail.com wrote:

Hi,

I’m trying this on my server:

pg_restore -i -d dhis2_demo -v dhis2demo.backup

but all I get is this:

pg_restore: [archiver] input file does not appear to be a valid archive

Is this likely to be because I still have 8.4 on the server? If so, any tips on upgrading (I have lots of data on the server)?

Knut

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293

–
Cheers,
Knut Staring

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Cheers,
Knut Staring

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

10

Thanks Jan Henrik.

Ola

···

On 4 May 2011 15:21, Jan Henrik Øverland janhenrik.overland@gmail.com wrote:

Just to clear up - pgAdmin can export:

  • BACKUP

  • compressed backup

  • imported by pgAdmin restore, not by the psql command

  • called .backup

  • TAR

  • SQL

  • imported by pgAdmin restore, not by the psql command

  • PLAIN

  • postgres’ plain text dump format like Ola said, the same as using the pg_dump command

  • imported by the psql command, can not be imported by pgAdmin restore

  • should not be called .backup, but .pgdump or .pgplain etc

On Wed, May 4, 2011 at 14:33, Knut Staring knutst@gmail.com wrote:

On Wed, May 4, 2011 at 2:21 PM, Ola Hodne Titlestad olati@ifi.uio.no wrote:

On 4 May 2011 14:12, Knut Staring knutst@gmail.com wrote:

On Wed, May 4, 2011 at 2:01 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

What is the file format? Is it a plain text Postgres dump or has it been created using pg_dump as a compressed dump?

I am talking about the demo db downloaded from dhis2.org, which has the suffix .backup but maybe should rather be names .sql for clarity - since psql is the right tool to restore it.

Just above that download link is says:
“The PostgreSQL file must be unzipped and can be imported through pgAdmin restore function or with psql -d dbname -U username -f dhis2demo.backup” :slight_smile:

Well - sadly it is not possible to create an idiot proof point&click interface for those of us with such short memory that I didn’t even remember having I asked Lars to correct the filename in that exact command last week…

Must be getting really old…

k

It is not real sql either, but postgres’s plain text dump format.

Ola

Knut

You are right, as you will not be able to (easily) restore a version of the database created with Postgres 9 to postgres 8.4. You should be able to restore from a plain text dump however.

On Wed, May 4, 2011 at 1:40 PM, Knut Staring knutst@gmail.com wrote:

Hi,

I’m trying this on my server:

pg_restore -i -d dhis2_demo -v dhis2demo.backup

but all I get is this:

pg_restore: [archiver] input file does not appear to be a valid archive

Is this likely to be because I still have 8.4 on the server? If so, any tips on upgrading (I have lots of data on the server)?

Knut

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293

–
Cheers,
Knut Staring

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Cheers,
Knut Staring

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

11

Also, as a side note…

Compressed backup files begin with something like this…

PGDMP​:smiling_face::female_sign:

:smiling_face::smiling_face::smiling_face:

They are binary files and cannot be restored between different versions of the Postgresql server (easily).

Plain text dumps begin with something like this…

···

–

– PostgreSQL database dump

–

– Dumped from database version 9.0.1

– Dumped by pg_dump version 9.0.1

– Started on 2011-05-02 06:53:17

They (usually) can be restored between different versions of Postgres.

Although the file extension can help, there is no requirement that they end with .backup or .pgplain or even have any extension whatsoever. This is entirely at the discretion of whoever created the backup file. However, they are easily to identify by peeking at the first few lines of the file.

Regards,

Jason

On Wed, May 4, 2011 at 3:25 PM, Ola Hodne Titlestad olati@ifi.uio.no wrote:

On 4 May 2011 15:21, Jan Henrik Øverland janhenrik.overland@gmail.com wrote:

Just to clear up - pgAdmin can export:

  • BACKUP

  • compressed backup

  • imported by pgAdmin restore, not by the psql command

  • called .backup

  • TAR

  • SQL

  • imported by pgAdmin restore, not by the psql command

  • PLAIN

  • postgres’ plain text dump format like Ola said, the same as using the pg_dump command

  • imported by the psql command, can not be imported by pgAdmin restore

  • should not be called .backup, but .pgdump or .pgplain etc

Thanks Jan Henrik.

Ola

On Wed, May 4, 2011 at 14:33, Knut Staring knutst@gmail.com wrote:

On Wed, May 4, 2011 at 2:21 PM, Ola Hodne Titlestad olati@ifi.uio.no wrote:

On 4 May 2011 14:12, Knut Staring knutst@gmail.com wrote:

On Wed, May 4, 2011 at 2:01 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

What is the file format? Is it a plain text Postgres dump or has it been created using pg_dump as a compressed dump?

I am talking about the demo db downloaded from dhis2.org, which has the suffix .backup but maybe should rather be names .sql for clarity - since psql is the right tool to restore it.

Just above that download link is says:
“The PostgreSQL file must be unzipped and can be imported through pgAdmin restore function or with psql -d dbname -U username -f dhis2demo.backup” :slight_smile:

Well - sadly it is not possible to create an idiot proof point&click interface for those of us with such short memory that I didn’t even remember having I asked Lars to correct the filename in that exact command last week…

Must be getting really old…

k

It is not real sql either, but postgres’s plain text dump format.

Ola

Knut

You are right, as you will not be able to (easily) restore a version of the database created with Postgres 9 to postgres 8.4. You should be able to restore from a plain text dump however.

On Wed, May 4, 2011 at 1:40 PM, Knut Staring knutst@gmail.com wrote:

Hi,

I’m trying this on my server:

pg_restore -i -d dhis2_demo -v dhis2demo.backup

but all I get is this:

pg_restore: [archiver] input file does not appear to be a valid archive

Is this likely to be because I still have 8.4 on the server? If so, any tips on upgrading (I have lots of data on the server)?

Knut

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293

–
Cheers,
Knut Staring

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Cheers,
Knut Staring

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260974901293