David Ellis Rogers

Ranch Hand
+ Follow
since Feb 18, 2015
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
1
In last 30 days
0
Total given
0
Likes
Total received
1
Received in last 30 days
0
Total given
1
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by David Ellis Rogers

Sorry, I sent the wrong screen shot as attachment.  Here it is
I had not been able to successfully Delete/Drop the 9.4 and 11 servers.  I would do the operation from the pgAdmin GUI, they disappear.  Then, when I exit pgAdmin and then later reenter it, 9.4 and 11 servers are there (but not always).  I believe this happened with 10 and 11 also. Currently 9.4, 10, and ll (I renamed it A) show.

So, I downloaded/installed 9.4, 10, and 11 hoping that new copies would solve the problem.  – That did not eliminate the Delete/Drop problem.

So, at one point while testing with psql I ran the following and received back multiple copies (some 71) of the results of the simple query.  Note:  the result, shown in the attachment, is correct, but the 71 occurences is not.  Seems like there are some rogue processes, but I do not know how to kill them.


(Does the world access allow access to anyone on the internet?)

See the attachment for the result.

Many thanks for help.
From the postgreSQL documentation:
just did a simple backup (for using SQL to restore).  The backup file includes the line SET idle_in_transaction statement (3rd line):


NOTE:  the previous case did not contain such a line.
I ran the same backup and restore again.  Same error.  Looked in the backup file, there is no

idle_in_transaction_session_timout

in the backup file.  It has to be something it adds, or some other process is running and inhibiting it from doing the pg_restore.  Suggestion?

I will try doing a simple backup.
I used pgAdmin 4  to backup pg1 on server 10.  Appendix 3 shows the options and that it was successful.

Then I used pgAdmin 4 to restore it, resulting in the "SET idle_in_transaction_session_timeout = 0;" error, as seen in Appendix 4.   From what I have gathered from the internet, this one involves another process not being completed.  I have even done a FULL VACUUM, but that doesn't solve it.

Any help would be greatly appreciated.
I have disconnected the 9.4 and 10 servers and then deleted/dropped them, but upon exiting and then reentering pgAdmin 4 they reappear.  How does one actually get rid of them?

I have attempted to do a backup of pg1 database on server 11 using pgAdmin 4’s backup, and received the error message in appendix 1.  So, I tried using pg_dump in the terminal.  The result is shown in appendix 2:  what am I doing wrong?

I have seen several messages to the pgAdmin 4 site that indicate it has several misgivings.  Perhaps I should return to pgAdmin 3?
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.
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.
The software should not allow it on the same machine.  On different machines is another matter.
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.
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?


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