How to make backups of Postgresql database on a production server?

Question

PostgreSQL

There is pg_dump. How to make backups of the Postgresql database on the server? By the crown, about once a day? And then upload them to aws and dropbox? Encrypt? How many copies to keep, that is, for how long - for the last week, month or ...? Maybe send it to soap, just in case? Something else?
In general, what and how?
I don't want to use third party services.

Answer 1

By the crown, about once a day?

It is possible and more often, depending on how sensitive it will be to lose data between backups. If it is very, very sensitive, you need to think about raising a replica or about a permanent backup (using WAL logs).
Anywhere, but be sure to upload somewhere to a completely separate server / service, with a check that everything was successfully uploaded.
And better - on 2 different storages.
Well, it’s purely optional, although I personally don’t see the point in this if you upload a backup to other servers / services.
Those. you can send to soap, or you can not send it, but uploading a backup to 1-2 other servers / services is necessary.
From personal experience. True admins, please correct me if I'm messing up somewhere.
I wrote a bash script that I run cron every 3 hours. Script
For now, I keep everything without cleaning out the old one (there are no problems with the place). Ideally, apparently, you need to store according to the scheme

• N (where N is a few tens) of the latest
  
• 4-6 last weekly
  
• 2-4 last monthly.
  

Answer 2

Depends on:
0) how much time you can afford to lose when restoring
1) how much space you can allocate for storing backups
2) how much time you want to save in order to be able to restore data sometime from a more distant past
How to shove through the mail even small backups of ten gigabytes?
Or are you asking about very tiny bases?
To encrypt or not - depends on security requirements and risk assessment in case of disaster recovery, not recovering the encryption key.
For a good point in time recovery - pg_basebackup + pg_receivexlog (archive_command only if you are sure that you can do it normally). Or something that works on top of them.
A separate case of pitr with storage in amazon - see quite intelligible wal-e
If pitr is not required, then do pg_dump. Yes, it is better to archive, dumps are compressed quite well.
For example, a backup policy from practice: daily pg_dumpall, are stored on the 1st of each month for a year plus 8 days of the previous daily ones.
Something else - regularly try to restore and run the database from the backup.