[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: [ADMIN] WG: postgres pg_basebackup
From: "Brandl, Wolfgang" <wolfgang.brandl () brz ! gv ! at>
Date: 2017-05-10 12:24:54
Message-ID: F2EB158D72DC6A4F97877DD0BB6F020E4E9564A8 () CMD0MBX-AB01 ! cmd0 ! cna ! at
[Download RAW message or body]
Hello
I have a database with the following tablespaces:
pgwb=# \db
List of tablespaces
Name | Owner | Location
------------+-------+-------------------------------------
bspace | pgwb | /data1/tablespaces/TS_B_SPACE/tests
meta | pgwb | /data1/tablespaces/metadata/tests
mspace | pgwb | /data1/tablespaces/TS_M_SPACE/tests
nspace | pgwb | /data1/tablespaces/TS_N_SPACE/tests
pg_default | pgwb |
pg_global | pgwb |
uspace | pgwb | /data1/tablespaces/TS_U_SPACE/tests
(7 rows)
They are referenced in as symbolic links in:
pgwb@BLIXSQL0:/data1/pgwb/pg_tblspc> ls -l /data1/pgwb/pg_tblspc
total 0
lrwxrwxrwx 1 pgwb users 35 May 9 09:17 16536 -> /data1/tablespaces/TS_U_SPACE/tests
lrwxrwxrwx 1 pgwb users 35 May 9 09:17 16537 -> /data1/tablespaces/TS_B_SPACE/tests
lrwxrwxrwx 1 pgwb users 35 May 9 09:17 16538 -> /data1/tablespaces/TS_N_SPACE/tests
lrwxrwxrwx 1 pgwb users 35 May 9 09:17 16539 -> /data1/tablespaces/TS_M_SPACE/tests
lrwxrwxrwx 1 pgwb users 33 May 9 09:17 16540 -> /data1/tablespaces/metadata/tests
I have setup the database for PIT recovery.
To make a base backup I use pg_basbackup like:
pg_basebackup --xlog --format=t -D /data1/backup/`date +%Y%m%d`
In the backup folder I have the following files:
pgwb@BLIXSQL0:/data1/backup/20170510> ls -l
total 62772
-rw-r--r-- 1 pgwb users 289792 May 10 13:51 16536.tar
-rw-r--r-- 1 pgwb users 2821120 May 10 13:51 16537.tar
-rw-r--r-- 1 pgwb users 2821120 May 10 13:51 16538.tar
-rw-r--r-- 1 pgwb users 371712 May 10 13:51 16539.tar
-rw-r--r-- 1 pgwb users 716800 May 10 13:51 16540.tar
-rw-r--r-- 1 pgwb users 57170432 May 10 13:51 base.tar
Now a crash happens I have to restore from this tar files, but I don't know the \
symbolic link destination.
How can I solve this?
Do I need a pg_dump from the tablespaces?
Regards
Wolfgang
Telefon: +43 1 711 23 - 883332
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"> <head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Nur Text Zchn";
margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
span.NurTextZchn
{mso-style-name:"Nur Text Zchn";
mso-style-priority:99;
mso-style-link:"Nur Text";
font-family:"Calibri","sans-serif";}
span.E-MailFormatvorlage19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.crayon-v
{mso-style-name:crayon-v;}
span.crayon-h
{mso-style-name:crayon-h;}
span.crayon-o
{mso-style-name:crayon-o;}
span.crayon-sy
{mso-style-name:crayon-sy;}
span.crayon-r
{mso-style-name:crayon-r;}
span.E-MailFormatvorlage25
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="DE-AT" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoPlainText"><span lang="EN-US">Hello<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">I have a database with the following \
tablespaces:<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US" \
style="font-family:"Courier New"">pgwb=# \db<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New""> \
List of tablespaces<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US" \
style="font-family:"Courier New""> Name \
| Owner | \
Location<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US" \
style="font-family:"Courier \
New"">------------+-------+-------------------------------------<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">bspace | pgwb | \
/data1/tablespaces/TS_B_SPACE/tests<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">meta | pgwb | \
/data1/tablespaces/metadata/tests<o:p></o:p></span></p> <p class="MsoPlainText"><span \
lang="EN-US" style="font-family:"Courier \
New"">mspace | pgwb | \
/data1/tablespaces/TS_M_SPACE/tests<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">nspace | pgwb | \
/data1/tablespaces/TS_N_SPACE/tests<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">pg_default | pgwb |<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">pg_global | pgwb |<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">uspace | pgwb | \
/data1/tablespaces/TS_U_SPACE/tests<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">(7 rows)<o:p></o:p></span></p> <p class="MsoPlainText"><span \
lang="EN-US">They are referenced in as symbolic links in:<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">pgwb@BLIXSQL0:/data1/pgwb/pg_tblspc> ls -l \
/data1/pgwb/pg_tblspc<o:p></o:p></span></p> <p class="MsoPlainText"><span \
lang="EN-US" style="font-family:"Courier New"">total \
0<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US" \
style="font-family:"Courier New"">lrwxrwxrwx 1 pgwb users 35 May 9 \
09:17 16536 -> /data1/tablespaces/TS_U_SPACE/tests<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">lrwxrwxrwx 1 pgwb users 35 May 9 09:17 16537 -> \
/data1/tablespaces/TS_B_SPACE/tests<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">lrwxrwxrwx 1 pgwb users 35 May 9 09:17 16538 -> \
/data1/tablespaces/TS_N_SPACE/tests<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">lrwxrwxrwx 1 pgwb users 35 May 9 09:17 16539 -> \
/data1/tablespaces/TS_M_SPACE/tests<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">lrwxrwxrwx 1 pgwb users 33 May 9 09:17 16540 -> \
/data1/tablespaces/metadata/tests<o:p></o:p></span></p> <p class="MsoPlainText"><span \
lang="EN-US"><o:p> </o:p></span></p> <p class="MsoPlainText"><span \
lang="EN-US">I have setup the database for PIT recovery.<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US">To make a base backup I use pg_basbackup \
like:<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US" \
style="font-family:"Courier New"">pg_basebackup --xlog --format=t -D \
/data1/backup/`date +%Y%m%d`<o:p></o:p></span></p> <p class="MsoPlainText"><span \
lang="EN-US" style="font-family:"Courier New""><o:p> </o:p></span></p> \
<p class="MsoPlainText"><span lang="EN-US">In the backup folder I have the following \
files:<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US" \
style="font-family:"Courier New"">pgwb@BLIXSQL0:/data1/backup/20170510> \
ls -l<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US" \
style="font-family:"Courier New"">total 62772<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">-rw-r--r-- 1 pgwb users 289792 May 10 13:51 \
16536.tar<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US" \
style="font-family:"Courier New"">-rw-r--r-- 1 pgwb users 2821120 May \
10 13:51 16537.tar<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US" \
style="font-family:"Courier New"">-rw-r--r-- 1 pgwb users 2821120 May \
10 13:51 16538.tar<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US" \
style="font-family:"Courier New"">-rw-r--r-- 1 pgwb users \
371712 May 10 13:51 16539.tar<o:p></o:p></span></p> <p class="MsoPlainText"><span \
lang="EN-US" style="font-family:"Courier New"">-rw-r--r-- 1 pgwb \
users 716800 May 10 13:51 16540.tar<o:p></o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US" style="font-family:"Courier \
New"">-rw-r--r-- 1 pgwb users 57170432 May 10 13:51 \
base.tar<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US" \
style="font-family:"Courier New""><o:p> </o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p> <p \
class="MsoPlainText"><span lang="EN-US">Now a crash happens I have to restore from \
this tar files, but I don’t know the symbolic link \
destination.<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US">How can \
I solve this?<o:p></o:p></span></p> <p class="MsoPlainText"><span lang="EN-US">Do I \
need a pg_dump from the tablespaces?<o:p></o:p></span></p> <p \
class="MsoPlainText">Regards<o:p></o:p></p> <p \
class="MsoPlainText">Wolfgang<o:p></o:p></p> <p \
class="MsoPlainText"><o:p> </o:p></p> <p \
class="MsoPlainText"><o:p> </o:p></p> <p \
class="MsoPlainText"><o:p> </o:p></p> <p class="MsoPlainText">Telefon: \
+43 1 711 23 - 883332<o:p></o:p></p> <p \
class="MsoPlainText"><o:p> </o:p></p> <p \
class="MsoPlainText"><o:p> </o:p></p> </div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic