Date: Tue, 10 Mar 2020 20:37:27 -0400
From: Stephen Frost <sfrost@snowman.net>
To: mimble9@danwin1210.me
Cc: pgsql-novice@lists.postgresql.org
Subject: Re: pgbackrest creating new directories (messing up cron jobs).
Message-ID: <20200311003726.GR3195@tamriel.snowman.net>
References: 
 <5808963daa5dd7b4a3704bba8dac828b.squirrel@danielas3rtn54uwmofdo3x2bsdifr47huasnmbgqzfrec5ubupvtpid.onion>
 <20200310134448.GA3195@tamriel.snowman.net>
 <c13c5fb8c0b95a9bb68f7ecefb829c7f.squirrel@danielas3rtn54uwmofdo3x2bsdifr47huasnmbgqzfrec5ubupvtpid.onion>
 <20200310223753.GO3195@tamriel.snowman.net>
 <f15eb088fd457eb9c42c03f5d8628baa.squirrel@danielas3rtn54uwmofdo3x2bsdifr47huasnmbgqzfrec5ubupvtpid.onion>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="NHtf4wESLNcCqhSM"
Content-Disposition: inline
In-Reply-To: 
 <f15eb088fd457eb9c42c03f5d8628baa.squirrel@danielas3rtn54uwmofdo3x2bsdifr47huasnmbgqzfrec5ubupvtpid.onion>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--NHtf4wESLNcCqhSM
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* mimble9@danwin1210.me (mimble9@danwin1210.me) wrote:
> I think I need to take a step backwards and ask something else before
> moving forward.

Good idea.

> > Isn't that going to mail you the same WAL over and over again if you set
> > it up as a cronjob..?  Is that really what you want?
>=20
> I took a full backup when I started. Then I used type=3Ddiff. This creates
> two archives every x hours (depending on the time period set in cron).

WAL files are not "archives" in the sense that they are a complete
database backup- they're just the write-ahead-logs from the ongoing
running of PG.  You need the actual data files as well as the WAL to
perform a restore.

> For example:
>=20
> -rw-r----- 1 postgres postgres 27145 Mar 11 00:00
> 0000000100000001000000B9-6f3902fe5c3bdebc3c1c124ec6821c7206e350da.gz
>=20
> -rw-r----- 1 postgres postgres 27126 Mar 11 00:00
> 0000000100000001000000BA-214e7142c6eda0a350577f6bd624c3db203e184f.gz
>=20
> Only one of these relates to the database I setup in PostgreSQL. I don't
> know what the other one is but it might be related to the "default"
> database 'postgres'. (This is just a guess).

No, that's not how WAL works.  Those are two WAL files and they're both
generated as part of running the PG system- they are not specific to one
database or another in PG.

> Irrespective, I would only want a new archive file if something has
> changed in the database. Yet I seem to always receive two new files every
> x hours even if nothing has changed.

These individuals files are not archives of the entire PG system or
anything you can use to reconstruct a running PG system with just one
file.

> So I tried type-incr but that seems to produce the same results as type=
=3Ddiff.

The difference between incremental and differential backups is
documented at https://pgbackrest.org

> I wonder what I am doing wrong?

I'm afraid there are some pretty fundamental misunderstandings that you
have about the PG WAL, PG backups, and what you can do with a PG cluster
andf file-level backup/restore.

Documentation about the WAL can be found here:

https://www.postgresql.org/docs/current/wal-intro.html

If what you're actually looking for is a single file that has a backup
of your entire database, you can get that by running:

pgbackrest backup --archive-copy

and then do:

pgbackrest restore --pg-path=3D/path/to/somewhere
tar -czf pgdata.tar.gz /path/to/somewhere

or so.  Check the pgbackrest docs for the specific command syntax and
such.

Thanks,

Stephen

--NHtf4wESLNcCqhSM
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJeaDLGAAoJEO1sijiDR2RV+gAP/0nihibinJCErW1voeWwNGN5
UDPbpdQB5deiNomxGwhH/YImo1q6tcA7rWtx6i1pBa9MZwJP04qadxBbLi0Qd8Eh
N+PJLPVl1KOWOIHIYGlDnLdmgc93CLowLEByXAAbnMHkUSov5VnI6TaKTG4h4cVo
jnhYADJdHLJC3XiisuGZ6vaMbnbxCFry8ivIGgTuwlC7X6SmVt7wjk3j2etPIMO3
vnVmSpVndWqCYJLUpltiR/5frzqms0uKnsADBznrGglqOln3GbAMm4r1Fywsv9LL
2NA/Rnd1f4mIa3nzp/O0ysKqmWcL/tRvUl/7GuYeGFdGalk+yFwVPO9zHDC6JXon
+3p669q8htfEC4UaktUJsRYxl1S3p84mGkTNEG8KYrlXSJUqk1Csjk/rnSHVeBkp
WBCUvNLH1mBC/+dT1sJ7Y5jbSVSk3dKkxbASEaXSetW8X5TC293NN/7vw2dUMHyK
NVNbNbT4xqHvw6Gl4W99WdKWaxt458vMAffAFkSSxL1kQ5egM7YnOqzmuxn45Wjy
trzevDPaiDFuDc4TVAqSEkg4iDH6ZazGPLLesNa/4t9zP4a4Y591a0m5t1ip7TL4
YwGnkx2FVk53DTEfsvYctby1gJt29N41vovQ/fdyGVGXi/Pl5MPLcAJxtWjibo0P
J0fOiVyuSaY90bUn493H
=nDuH
-----END PGP SIGNATURE-----

--NHtf4wESLNcCqhSM--