• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

PostgreSQL 10 backup not working

 
Ranch Hand
Posts: 51
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Request for Help on PostgreSQL Backup and Restore

I am trying to create a backup/restore procedure for PostgreSQL 11 on a MacBook Pro running macOs Mojave, version 10.14.5.
I have the following databases, where pg1 is a working one. I have recently downloaded the new version of PostgreSQL, version 11.
Somehow I was able to bring pg1 over to the version 11 (but did not write the procedure down for future use).

Since I believe in keeping software up to date, I would like to destroy the versions 9.4 and 10.  This would be OK, correct?

My problem is that somehow I cannot get the backup to work on the pg1 under PostgreSQL 11.
In the following, why is it not accepting the pg_dump?


Have spent an enormous amount of time on this.  Would greatly appreciate help.
Screen-Shot-2019-06-13-at-5.07.15-PM.png
[Thumbnail for Screen-Shot-2019-06-13-at-5.07.15-PM.png]
The Databases
 
Bartender
Posts: 20833
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Apparently because you are attempting to run the pg_dump program as though it was a command to the psql client program.

pg_dump and pg_restore are full-fledged programs in their own right and should be run directly from the command shell, not from the psql app.
 
David Ellis Rogers
Ranch Hand
Posts: 51
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have been working on it, have been learning how to navigate around the terminal.  I am under the impression that I need to create the database name before doing the pg_dump.  (See the first 2 lines below:)


Why is the create command not recognized but the pg_dump it seemingly is?
 
Tim Holloway
Bartender
Posts: 20833
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do yourself a favor and don't mess directly with the postgresql files. Most of them aren't intended for human interaction and some can be for legacy reasons but are better manipulated via postgres itself. If you do need to do something special, the documentation will tell you.

The createdb program may have a different name depending on which OS distro you are using. I can use it for CentOS, but the problem with generic commands like that is that a lot of systems when you say "createdb", the question is what type of database? I mean, it could be a MySQL or SQLite database. So sometimes distro providers alter the names of utility programs to eliminate ambiguity.

In any event, you can't pg_dump a database that doesn't exist.

But a better solution than createdb is to use the psql command utility and issue the "CREATE DATABASE pg222;" DDL command. That works for most databases, and I just verified that PostgreSQL is included. In fact:

createdb man page wrote:
      createdb is a wrapper around the SQL command CREATE DATABASE (CREATE_DATABASE(7)). There is no effective difference between creating databases via this utility and via other methods for
      accessing the server.




Note however, that PostgreSQL is very paranoid about security. So if the user that attempts to create the database doesn't have database creation rights, the CREATE DATABASE command will be rejected. This can be avoided by running the psql command under the postgresql administrative userid - which is NOT the same thing as the OS administrative (root) userid. Usually the pg admin userid is something like 'postgres'.

If you do that, however, you'll need to grant rights to the userid who is supposed to actually own the database. The easiest way to to that is by appending the "WITH OWNER = my_user_id" to the CREATE DATABASE command. Use the psql command "\h create database" to see the full set of options.
 
David Ellis Rogers
Ranch Hand
Posts: 51
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have been working on it.  Cannot get it to bring up psql.  Something strange is happening.  Notice the server version 9.4 in the following.

Last login: Mon Jun 17 15:57:26 on ttys000
Davids-MacBook-Pro:~ davidellisrogers$ pwd
/Users/davidellisrogers
Davids-MacBook-Pro:~ davidellisrogers$ postgres -V
-bash: postgres: command not found
Davids-MacBook-Pro:~ davidellisrogers$ locate bin/postgres
/Library/PostgreSQL/10/bin/postgres
/Library/PostgreSQL/11/bin/postgres
/Library/PostgreSQL/11/debug_symbols/bin/postgres.dSYM
/Library/PostgreSQL/11/debug_symbols/bin/postgres.dSYM/Contents
/Library/PostgreSQL/11/debug_symbols/bin/postgres.dSYM/Contents/Info.plist
/Library/PostgreSQL/11/debug_symbols/bin/postgres.dSYM/Contents/Resources
/Library/PostgreSQL/11/debug_symbols/bin/postgres.dSYM/Contents/Resources/DWARF
/Library/PostgreSQL/11/debug_symbols/bin/postgres.dSYM/Contents/Resources/DWARF/postgres
/Library/PostgreSQL/9.4/bin/postgres
Davids-MacBook-Pro:~ davidellisrogers$ /usr/lib/postgresql/9.4/bin/postgres -V
-bash: /usr/lib/postgresql/9.4/bin/postgres: No such file or directory
Davids-MacBook-Pro:~ davidellisrogers$ /davidellisrogers/Library/Postgressql/9.4/bin/postgres -V
-bash: /davidellisrogers/Library/Postgressql/9.4/bin/postgres: No such file or directory
Davids-MacBook-Pro:~ davidellisrogers$  /davidellisrogers/Library/Postgressql/9.4/bin/postgres: No such file or directory
-bash: /davidellisrogers/Library/Postgressql/9.4/bin/postgres:: No such file or directory
Davids-MacBook-Pro:~ davidellisrogers$ /davidellisrogers/Library/Postgresql/9.4/bin/postgres -V
-bash: /davidellisrogers/Library/Postgresql/9.4/bin/postgres: No such file or directory
Davids-MacBook-Pro:~ davidellisrogers$ pwd
/Users/davidellisrogers
Davids-MacBook-Pro:~ davidellisrogers$ psql -V
-bash: psql: command not found
Davids-MacBook-Pro:~ davidellisrogers$

As shown in the attached pic of the tree structure, I have 3 servers: 9.4, 10 (which I have been using recently) and 11 which I last week downloaded because I believe in keeping up-to-date. In both 9.4 and 10 there are 18 identical tables in pg1.  I deleted one table in pg1 under version 11, its tables also appear in pg1 under 9.4 and 11.  I think it is confused.  - That is probably an issue; but, also, why can I not use psql?  Should I destroy servers 9.4 and 11?  Also, when I sign in (su postgres) it wants a password, which I do not have.  - I have really screwed up.

Thanks for your help.
Screen-Shot-2019-06-17-at-4.45.42-PM.png
[Thumbnail for Screen-Shot-2019-06-17-at-4.45.42-PM.png]
Tree Structure
Screen-Shot-2019-06-17-at-12.28.58-AM.png
[Thumbnail for Screen-Shot-2019-06-17-at-12.28.58-AM.png]
 
Tim Holloway
Bartender
Posts: 20833
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yep, that's a mess alright.

I'm presuming that you're running either Linux or MacOS. In which case, PostgreSQL is normally installed via the distro's package manager. There are several packages required for postgresql, depending on whether you want to be a postgres client, a postgres server, or both. And there are probably some common-code packages that are required regardless of whether you're a client or a server, but those usually get pulled in as depedencies.

So, for example, I have a machine set up as a client running CentOS 7. The packages that yum installed were:

postgresql-libs-9.2.24-1.el7_5.x86_64
postgresql-9.2.24-1.el7_5.x86_64

This is, of course, postgresql 9.2.

Under ordinary circumstances, you cannot have multiple versions of postgresql installed on one machine. Most people don't need to, and thus the package structure won't allow it. In any event, since the postgresql server is a network server, and one and ONLY one application can listen on port 5432, you cannot run multiple postgresql servers at one time withough a lot of extra trouble and that's beyond the level that I think you're at.

In fact, if you have been doing something awful like downloading and building PostgreSQL from source code (which, alas, is the first thing that a lot of popular Unix-style products tell you to do), then I recommend you dump all that and use apt, yum, dnf or whatever to install the official distro version of the PostgreSQL server for that machine.

To use the psql utility and also pg_dump, pg_restore and so forth, you may need to install a postgres-client package. Also, the postgresql JDBC driver often has a separate package of its own.

The PostgreSQL server (once known as the "postmaster") is expected to run as a daemon, so for modern Linux distros, you'd install the postgresql-server package and activate it using the following commands (as root):


That will ensure that the server starts and will automatically restart when you reboot.

Note that the names of services and packages may vary if you are using some other distro such as Ubuntu, but the basic concepts remain the same.

I'm not sure what you used to get the browser that shows all those servers. I mostly just run everything from the command line. Your final screen shot does seem to show a successful backup, though.

The directory structure is really weird, though. MacOS?
 
David Ellis Rogers
Ranch Hand
Posts: 51
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am running macOS Mojave 10.14.5 on a MacBook Pro, also using Eclipse and Java.  I use PostgreSQL’s Stack Builder to install PostgreSQL .  (The options are 9.4 or 10 on port 5432 or 11 on port 5433.)  I have the JDBC driver and some other Stack Builder options.  The browser I use (and is in the pic I sent) is PostgreSQL’s GUI pgAdmin 4.0.

Only the pg1 database is of interest to me.  It contains data in 4 tables that I would like to retain (though I could rebuild them).  The other tables I would like to retain the definitions of.  The pg1 database in servers 9.4 and 10 both have the same identical tables (18); while 11 has only 17, as I deleted one table.  I believe I have a good (“simple”) backup having SQL commands.

So, what should my plan be, something like the following?
1. Destroy the 9.4 and 10 servers, then
2. see if 11 works with existing pg1 database.
If not:
3. Delete existing pg1 database
4. Restore pg1 database
5. See if it works
If not:  
1. Destroy the 11 server, then
2. Using Stack Builder, download/install 11
3. Restore pg1.
4. Test
?

Thanks for all your assistance!
 
Tim Holloway
Bartender
Posts: 20833
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Much clearer.

Ordinarily, I'd recommend the OS native package installer, which is probably brew for MacOS, but if your intent is to run multiple servers on multiple versions, Stack Builder should be more flexible, if less targeted to the specific OS.

I'm assuming that you're basically trying to migrate from 2 earlier databases to a newer version. In which case, I'd generally deal with one at a time, starting with the oldest. And in an era of terabyte disks, I probably wouldn't actually go deleting/uninstalling until a given database had been fully migrated.

You're looking at 2 different types of backups. The "backup data" backup is a full backup. The "backup table structure" is a schema backup. There's a pg_dump option for that.

You should definitely backup to SQL, and preferably uncompressed SQL. It's not a bad idea to make an offline copy of the SQL just in case. PostgreSQL has had an infuriating history of major incompatibilities in their internal binary files even between minor releases, so using a regular brute-force disk backup utility isn't as safe as SQL. Their SQL is, for the most part, going to be backwards compatible, and indeed, I think there may even be an option to make it more compatible for restoring to non-PostgreSQL DBMS databases.

I'd do the backups first, and only start deleting servers if resources get critical. Then as each restore checks off, you can uninstall the old server. For extra paranoia, as I said, make offline copies of the backups and for good measure make note of which backups went with which server.

PostgreSQL supports named schemas. Normally a database is created with the schema name "default", but if you need to host 2 different versions of the same database at the same time - for example, to merge stuff between different generations of the database, then you can restore the backup with another schema name. You then have to qualify which schema name you're working with, but that can make things a lot simpler than something like messing around with the backup SQL.

And, of course, if you are going to do stuff like that, be sure and take backups periodically as you work through things so that if disaster strikes you won't have to start from the beginning again!

 
David Ellis Rogers
Ranch Hand
Posts: 51
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In the following, the file name is coded:  e.g., 06262019h10, where the 06 stands for June, 26 is the day of the month, 2019 is the year, h designates a specific run on that day and 10 indicates the server number.  In all my tests I use pgAdmin 4 GUI, selecting YES on options pre-data, data, post-data and verbose messages, all others have NO selected.

One test that I did was using version 11 of the database server but it selected 10.  The top line of the message output said “Process Watcher – Restoring backup on the server ‘PostgreSQL 11 (localhost:5433)”, then:

Running command:
/Library/PostgreSQL/10/bin/pg_restore –host “localhost” –port ”5433” –username “postgres” –no-password –dbname “pg2” –section=pre-data –section=data –section=post-data –verbose “/Users/davidelllisrogers/06262019h10”.
Then it gives the start time followed by a one-line message “pg_restore: [archiver] input file is too short (read 0, expected 5)”

I have checked:  there is a pg_restore in /Library/PostgreSQL/10/bin and also in /Library/PostgreSQL/11/bin.  How do I force it to use 11?  I would like to be up-to-date.  Also, on this run, I looked at “/Users/davidellisrogers/06262019h10” and find that it is empty (probably the reason for the input file is too short).

- Bad news, so a new approach.

I did a little testing on what happens by adding something in a relation and then seeing what effect the addition had on the same database in the other servers.
Note:  the database, pg1 exists (is accessible) on all 3 servers.

values in work.wid   abcdefghi
added that value in database under server   9.41011
shows in version number9.4 YESNO
shows in version number10YES  NO
shows in version number11NONO

Conclusion:  the pg_restore on servers 9.4 and 10 are the same (at least for contents of a relation); the one on 11 is not working.  – The same holds for deleting a tuple.

I would like to move to server 11 version.  I downloaded version 11 again before the above table of tests, thinking I might have had a bad download of 11.
Any suggestions?  I am willing to delete 9.4 and 11, remain with 10.  I have just backed everything up to an external hard drive.
 
Tim Holloway
Bartender
Posts: 20833
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You need a command line. You can then select which backup program you want like so:



Note that when you do it this way you MUST be sure that you are backing up the right DBMS, so you may have to supply host address and port as well as database name(s) as command-line parameters.

If your GUI tool has a backup option, I would HOPE that they automatically select the right set of backup and restore utilities to go with the database you're talking to.

Either way, if's not merely "good" to use the right version of PostgreSQL utilities. PostgreSQL is infamous for making incompatible changes to the database file structure even between minor releases. So if you use the wrong version of backup/restore, there is the potential for serious mayhem.
 
David Ellis Rogers
Ranch Hand
Posts: 51
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your last paragraph really bothers me.  I have spent days trying to get pgAdmin 4 to dump and restore.  Something always goes wrong.  In all my years of computing, I have never encountered anything so obtuse.  Thus, i have downloaded homebrew.  I cannot find anywhere how to use a dump and restore.  Looks like I have to install postgreSQL using home-brew?

Thanks for your help.
 
Tim Holloway
Bartender
Posts: 20833
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's no easy task running multiple versions of the same software on the same computer to begin with, no matter what the software is (except for Java!). Be glad you're not trying to run multiple versions of Internet Explorer.

Your GUI interface looks capable, but I don't know how reliable it may be. It's probably not backed by either Apple or the PostgreSQL group, so quality might not be as tight. Still, if it's installing postgres servers on your system, then it's almost certainly either building postgresql from scratch or running brew under cover. And if brew is anything like the Linux package managers, it has an inventory command that can tell you about what packages were installed.

I can tell you that in Linux, the easiest way to run multiple versions of postgresql is to put each one in its own container or VM. Though it's not impossible that alternative packaging might be available as well, the official distros only support one specific postgresql version (usually).
 
David Ellis Rogers
Ranch Hand
Posts: 51
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I decided to give the backup using the pgAdmin 4 GUI one last chance.  First, see project tree attachment.

Note that servers 9.4 and 10 are disconnected.
I specified the following for a backup on pg1:
Format:  plain
Compression ratio:  0
Encoding: blank
Dump Options:  Pre-data, Data, and Post-data:  YES
Type of objects:  all NO
Do not save:  Owner, Privilege, and Tablespace:  YES; Unlogged Table Data:  No
Queries:  Use Column Inserts, Use Insert Commands, and Include CREATE DATABASE
STATEMENT:  YES; Include DROP DATABASE statement:  NO
Disable:  Trigger (disabled); $ Quoting:  NO
Miscellaneous:  Verbose Messages:  YES; With OIDs, Force double quotes on, and Use SET SESSION AUTHORIZATION:  NO.
The result is shown in the "Result" attachment.

Note:  I started the specification for pg1 on server PostgreSQL 11 and it wants to use pg_dump version 10.5 (see "Result" attachment).  Also:  Note that I previously had determined that servers 9.4 and 10 are working on the same database, whereas server 11 is not (it is working on its own).

Either this is a bug or I have set one of the options wrong.

Do you think I should report this as a bug?


Screen-Shot-2019-07-03-at-2.23.04-PM.png
[Thumbnail for Screen-Shot-2019-07-03-at-2.23.04-PM.png]
Project Tree showing servers
Screen-Shot-2019-07-03-at-1.11.42-PM.png
[Thumbnail for Screen-Shot-2019-07-03-at-1.11.42-PM.png]
Result
 
Tim Holloway
Bartender
Posts: 20833
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Red Alert! Red Alert! Red Alert!

Note that I previously had determined that servers 9.4 and 10 are working on the same database,



I hope you don't mean that literally. As in the same pg_data directory hosting the same database for 2 different server instances - AND 2 different versions of PostgreSQL.

Just sharing the same files between 2 instances would be bad enough - you could get major corruption since no DBMS I know of is designed for that and the interlocks needed would be punitive in performance.

But remember what I've said once or twice before? Even MINOR version releases of PostgreSQL are often not file-level compatible with each other. You should ALWAYS dump a database as SQL using the source database's pg_dump and import the database using the destination database's load utility. You cannot go à la carte on which utilities you use with which database.

PostgreSQL built a reputation for making absolutely no attempt to internally upgrade or be backwards compatible at the file level between different versions. Only a complete reload via SQL is considered totally safe. It's one of the biggest complaints I could make about it.
 
Tim Holloway
Bartender
Posts: 20833
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anyway, at least the PostgreSQL system didn't try and use mis-matched facilities on you. Nothing worse than thinking you succeeded when you actually didn't.

I still think your best bet is to use the raw command line and note that the command is basically as indicated except that you want to execute something like /Library/PostgreSQL/11/bin/pg_dump, not 10.

If you think that the pgAdmin app is defective, contact them here: https://www.pgadmin.org/support/list/  Note that pgAdmin isn't from the same group as PostgreSQL itself.
 
David Ellis Rogers
Ranch Hand
Posts: 51
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just did the following:
1. disconnected 9.4
2. connected 10
2. then added a row in 10’s work relation.
3. disconnected 10.
4.connected 9.4
5. looked at the work relation in 9.4:  the addition was there.
Next little experiment:
1. 9.4 has wid = 7
2. disconnect 9.4
3. connect 10
4. deleted row where wid =7
5. disconnect 10
6. connect 9.
7. wid = 7 is NOT there
Note:  both 9.4 and 10 have localhost as host (on my person Mac only)
Note 2:  Both servers have pg1 database

I think I will report this.
Also, I really appreciate your help.  I am returning to develop backup/restore only on server 10.
First I will test backing up only on  10, get that working; then delete server 11, then 9.4.
 
Rancher
Posts: 4114
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That does sound like the exact thing Tim was warning you about.
You can't just point different versions at the same database.

That it doesn't work is not a bug, it's not supposed to work.
 
David Ellis Rogers
Ranch Hand
Posts: 51
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The software should not allow it on the same machine.  On different machines is another matter.
 
Tim Holloway
Bartender
Posts: 20833
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

David Ellis Rogers wrote:The software should not allow it on the same machine.  On different machines is another matter.



Actually, no. The communications between client application and the postgresql server is done via network connection, even when it's the same machine. So to the tools, same machine, different machine, all looks the same.
 
David Ellis Rogers
Ranch Hand
Posts: 51
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Understand, thanks.  So should I go to the trouble for everyone, including me, to submit a "bug" request?  From what you two are saying, which makes sense, the network connection is some sort of router (which probably does not have the code to compare the connection requests and would mean some sort of drastic change to accommodate what idiots like me do).  Therefore it's not a "bug" (something not in accordance with the specification).  But I think there should be a warning in the postgreSQL documentation not to do such a thing.

Since connecting thru 10 to access the same database pg1 using pg_restore located at the desired /Lirary/PostgreSQL/10/bin works, I will first delete the server connected thru 9.4 and then the one connected thru 11.  If I lose the database, I have an external backup (and an external data dictionary from which I can rebuild everything).

Time to end this thread.
 
Tim Holloway
Bartender
Posts: 20833
125
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
pgAdmin has forums, and it's usually better to ask in the forum before filing an actual bug report.

The standard mechanism for postgresql client apps to talk to the postgresql server is a tcp/ip communication to the server's IP address 5432. Although other port numbers may be used if a single host has multiple instances of PostgreSQL services, since tcp allows only one application to own a given port on a host.

It does appear that attempting to contact a server with the wrong version of client (pg_dump) did, in fact cause a meaningful error message to be returned. Which is more than I can say for some products. As to the fact that there's no forward or backwards compatibility - which is something many products do allow - is definitely a common complaint. I haven't read the official docs lately, so I don't know if they explicitly warn you, but it's something you learn real quick.
 
David Ellis Rogers
Ranch Hand
Posts: 51
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Tim and Dave for all your help.  I don’t think I could have survived this ordeal without your assistance, Tim.

It’s time to put this to bed; I have spent too much time on trying to get backup/restore working correctly on server 10.  I have deleted 9.4 and 11 and the database thru the 10 server is working well, it has not changed.

I will mention this problem to the folks at the pgAdmin forum.

Again, many thanks.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!