Issue with PG_VERSION after restore

[Данило Глинський]

Hi. I'm using backup and restore with barman 1.5.1. I've done backup and recover, but connect to database fails.

I've started postgres at port 5434. Here is an error:

$ psql -U postgres -p 5434 -d game

psql: FATAL:  "pg_tblspc/16386/PG_9.3_201306121/62633" is not a valid data directory

DETAIL:  File "pg_tblspc/16386/PG_9.3_201306121/62633/PG_VERSION" is missing.
$

Hm... Let's see if this file is really absent (pgdata is my restore directory):

$ du -aL | grep PG_VERSION

4       ./pgdata/PG_VERSION

4       ./pgdata/base/204998/PG_VERSION

4       ./pgdata/base/204994/PG_VERSION

4       ./pgdata/base/12944/PG_VERSION

4       ./pgdata/base/1/PG_VERSION

4       ./pgdata/base/204997/PG_VERSION

4       ./pgdata/base/204995/PG_VERSION

4       ./pgdata/base/12949/PG_VERSION

4       ./pgdata/pg_tblspc/83786/PG_9.3_201306121/83790/PG_VERSION

4       ./pgdata/pg_tblspc/83786/PG_9.3_201306121/83787/PG_VERSION

4       ./pgdata/pg_tblspc/83786/PG_9.3_201306121/83791/PG_VERSION

4       ./pgdata/pg_tblspc/83786/PG_9.3_201306121/83788/PG_VERSION

4       ./pgdata/pg_tblspc/83786/PG_9.3_201306121/83789/PG_VERSION
$

Let's check the backup:

$ du -aL /media/dbbackup/barman/grog6/base/20151126T151127/ | grep PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/83786/PG_9.3_201306121/83790/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/83786/PG_9.3_201306121/83787/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/83786/PG_9.3_201306121/83791/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/83786/PG_9.3_201306121/83788/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/83786/PG_9.3_201306121/83789/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/data/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/data/base/204998/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/data/base/204994/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/data/base/12944/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/data/base/1/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/data/base/204997/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/data/base/204995/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/data/base/12949/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/77935/PG_9.3_201306121/78404/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/77370/PG_9.3_201306121/78280/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/77968/PG_9.3_201306121/78435/PG_VERSION

4       /media/dbbackup/barman/grog6/base/20151126T151127/16386/PG_9.3_201306121/62633/PG_VERSION
$

So, the PG_VERSION file is present in backup, but absent in restored dir. This is the command I do for recover:

RECOVER_DIR=/media/dbbackup/restore/pgdata

TBLSPC=/media/dbbackup/restore/tblspc

time sudo -u barman barman recover         \

  grog6 latest "$RECOVER_DIR"               \

     --tablespace game_tblspc:$TBLSPC      \

     --tablespace ratings_tblspc:$TBLSPC   \

     --tablespace reports_tblspc:$TBLSPC   \

     --tablespace wp_tblspc:$TBLSPC        \

     --tablespace analytics_tblspc:$TBLSPC

After that I start postgres with 

sudo -u postgres pg_ctl -D /media/dbbackup/restore/pgdata -o "-F -p 5434 -c max_connections=100 -c shared_buffers=20000" start

It starts in recover mode and here is log:

< 2015-11-26 17:42:43.906 UTC >LOG:  database system was interrupted; last known up at 2015-11-26 15:31:09 UTC

< 2015-11-26 17:42:43.928 UTC >LOG:  redo starts at D36/69434720

< 2015-11-26 17:45:39.208 UTC >LOG:  consistent recovery state reached at D36/919111C8

< 2015-11-26 17:47:10.765 UTC >LOG:  redo done at D37/5DFFF0E8

< 2015-11-26 17:47:10.766 UTC >LOG:  last completed transaction was at log time 2015-11-26 17:28:51.945278+00

< 2015-11-26 17:47:12.549 UTC >LOG:  database system is ready to accept connections

< 2015-11-26 17:47:12.549 UTC >LOG:  autovacuum launcher started

Now, I have such tablespaces:

postgres=# select * from pg_tablespace ;

     spcname      | spcowner | spcacl | spcoptions

------------------+----------+--------+------------

 pg_default       |       10 |        |

 pg_global        |       10 |        |

 game_tblspc      |    16385 |        |

 ratings_tblspc   |    77367 |        |

 reports_tblspc   |    77368 |        |

 analytics_tblspc |    77369 |        |

 wp_tblspc        |    83769 |        |

(7 rows)

All databases in wp_tblspc are accessible. But others result into error.

If I manually add PG_VERSION to tablespace dir, I get other error:

$ psql -U postgres -p 5434 -d game

psql: FATAL:  could not open relation mapping file "pg_tblspc/16386/PG_9.3_201306121/62633/pg_filenode.map": No such file or directory
$

After adding it to tablespace dir manually, I get:

$ psql -U postgres -p 5434 -d game

psql: FATAL:  could not open file "pg_tblspc/16386/PG_9.3_201306121/62633/12713": No such file or directory
$

How can I get access to my recovered database?

[Gabriele Bartolini]

Hi,

  why would you want to place all tablespaces in the same destination?

Cheers,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
 PostgreSQL Training, Services and Support
 gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

--
--
You received this message because you are subscribed to the "Barman for PostgreSQL" group.
To post to this group, send email to pgba...@googlegroups.com
To unsubscribe from this group, send email to
pgbarman+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/pgbarman?hl=en?hl=en-GB

---
You received this message because you are subscribed to the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgbarman+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Данило Глинський]

Thanks, that did the trick!
However I do not understand why multiple tablespaces cannot use one directory. Is this postgres limitation?

четвер, 26 листопада 2015 р. 23:28:31 UTC+2 користувач Gabriele Bartolini написав:

Hi,

  why would you want to place all tablespaces in the same destination?

Cheers,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
 PostgreSQL Training, Services and Support

 gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

[Gabriele Bartolini]

Hi,

  I would not call it limitation of PostgreSQL, rather a feature! ;)

  Yes, that's how tablespaces work in PostgreSQL, they are placeholders for a different directory on the file system.

Ciao,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
 PostgreSQL Training, Services and Support

 gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

2015-11-27 12:15 GMT+01:00 Данило Глинський <abcz2....@gmail.com>:

Thanks, that did the trick!
However I do not understand why multiple tablespaces cannot use one directory. Is this postgres limitation?

четвер, 26 листопада 2015 р. 23:28:31 UTC+2 користувач Gabriele Bartolini написав:

Hi,

  why would you want to place all tablespaces in the same destination?

Cheers,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
 PostgreSQL Training, Services and Support

 gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

[Данило Глинський]

thanks for fast feedback

пʼятниця, 27 листопада 2015 р. 15:04:57 UTC+2 користувач Gabriele Bartolini написав:

Hi,

  I would not call it limitation of PostgreSQL, rather a feature! ;)

  Yes, that's how tablespaces work in PostgreSQL, they are placeholders for a different directory on the file system.

Ciao,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
 PostgreSQL Training, Services and Support

 gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it

2015-11-27 12:15 GMT+01:00 Данило Глинський <abcz2....@gmail.com>:

Thanks, that did the trick!
However I do not understand why multiple tablespaces cannot use one directory. Is this postgres limitation?

четвер, 26 листопада 2015 р. 23:28:31 UTC+2 користувач Gabriele Bartolini написав:

Hi,

  why would you want to place all tablespaces in the same destination?

Cheers,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
 PostgreSQL Training, Services and Support

 gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it