CODEWITHSUNDEEP
postgresqlpostgresql-9.4
SinghVK
SinghVK

Reputation: 323

Need to check Postgres backups

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.

  1. 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.

  2. I need to check if backups are scheduled of databases. Can anyone guide me how I can check that?

  3. 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

Upvotes: 1

Views: 316

Answers (1)

Chris Curvey
Chris Curvey

Reputation: 10389

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.

Upvotes: 1

Related Questions