Need to check Postgres backups

Question

Recently, we got a requirement to support Postgres database. I am from the NoSql background and this is the first time I am putting my hands on postgres. We were given a server name and sudo access to Postgres user. The requirement was to make the server was high availability and to get familiar with the databases. I am able to login into the server and able to see the databases using psql interactive shell (\l) and users by (\du). I have a few questions.

I was given a task of creating a user for basic monitoring with basic privileges which I created using below

-bash-4.2$ createuser --interactive joe
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
Password:
-bash-4.2$ psql
Password:
psql (9.4.15)
Type "help" for help.

I need to check if backups are scheduled of databases. Can anyone guide me how I can check that?
I need to create a High availability of the server also. My plan for this was I will install Postgres in another server and will take a backup of all databases and restore it into that new server. The link I will follow is http://www.postgresqltutorial.com/postgresql-copy-database/.

Suggest me some ideas

First place to check existing backups would be to list your crontab — Slumdog
– Slumdog, Commented Aug 9, 2018 at 22:45

Chris Curvey · Accepted Answer · 2018-08-09 18:27:22Z

1

All good questions!

#1 Monitoring User

Looks like you got the basics right. Not sure if your user is going to need any more permissions than that.

#2 Are backups scheduled

You would work outside of Postgres to schedule backups. It's usually done by using cron (Unix) or Task Scheduler (windows) to kick off a pg_dump job.

#3 High Availability

Copying the database to another location is good (I would do that and then go play in there to "get familiar with the database"), but that's not high availability. Do a web search for "Postgresql Streaming Replication" and you'll find a bunch of tutorials on how to do this.

Oh, and you might want to do a web search for "postgresql when it's not your job" to find a great presentation on getting started as a Postgres DBA.

answered Aug 9, 2018 at 18:27

Chris Curvey

10.5k14 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SinghVK Over a year ago

Thanks Chris for your help, we are able to implement streaming replication in our psql servers. :)

Collectives™ on Stack Overflow

Need to check Postgres backups

1 Answer 1

#1 Monitoring User

#2 Are backups scheduled

#3 High Availability

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

#1 Monitoring User

#2 Are backups scheduled

#3 High Availability

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related