0

Let I first state that I am not a DBA-guy but I do have a question regarding restoring remote databases using PG Admin.

I have this PG Admin tool (v4.27) running in a Docker container and I use this portal to maintain two separate Postgress databases, both running in a Docker container as well. I installed PG Agent in both database containers and run scheduled daily backup's, defined via PG Admin and stored in the container of each corresponding databases. So far so good.

Now I want to restore one of these databases by using the latest daily backup file (*.sql), but the Restore Dialog of PG Admin only looks for files stored locally (the PG Admin container)?

Whatever I tried or searched for on the internet, to me it seems not possible to show a list of remote backup files in PG Admin or run manually a remote SQL file. Is this even possible in PG Admin? Running psql in the query editor is not possible (duh ...) and due to not finding the remote SQL-restore file I have no clue how to run this code within PG Admin on the remote corresponding database container.

The one and only solution so far I can think of, is scheduling a restore which has no calendar and should be triggered manually when needed, but it's not the prettiest solution.

Do I miss something or did I overlook the right documentation or have I created a silly, unmaintainable solution?

Thanks in advance for thinking along and kind regards,

Aad Dijksman

Aad Dijksman
  • 13
  • 3

2 Answers2

1

You cannot restore a plain format dump (an SQL script) with pgAdmin. You will have to use psql, the command line client.

COPY statements and data are mixed in such a dump, and that would make pgAdmin choke.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks for your reply .... in a hurry I forgot the most important issue I face in this solution and that is the fact that I have no access to a command line in this environment. This psql works like a charm but no option for me alas ..... – Aad Dijksman Sep 03 '21 at 07:18
  • You can install `psql` on your machine and restore the dump to a remote server. No need for shell access to the database machine. There is no alternative to `psql` for restoring such a dump. – Laurenz Albe Sep 03 '21 at 07:30
0

The solution by @Laurenz Albe points out that it is best to use the command line psql here, and that would be my first go-to.

However, if for whatever reason you don't have access to the command line and are only able to connect to this database via pgadmin, there is another solution which you can find here:

Export and import table dump (.sql) using pgAdmin

I recommend looking at the solution by Tomas Greif.

cazort
  • 516
  • 6
  • 19
  • Sharp reply, I indeed do not have access to a command line, hence my initial question. Thanks for this URL, right gonna read it. – Aad Dijksman Sep 03 '21 at 07:20