• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

Connection to PostgreSQL database problem

 
Ranch Hand
Posts: 58
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am running PostgreSQL on a MacBook Pro 16GB running OS X Yosemite 10.0.5.  I have databases running under PostgreSQL 9.4 (localhost:5432).  I downloaded PostgreSQL 10, because of a new datatype it contains.  Everything is fine using PostgreSQL 9.4, but when I try PostgreSQL 10 (localhost:5434) an error occurs: SERVER DOESN’T LISTEN.  I performed the following, which shows that pinging “localhost” succeeds.


I just now changed the port for PostgreSQL 10 to 5432, the same port that PostgreSQL 9.4 uses, it succeeds (no SERVER DOESN’T LISTEN). So, can now access the tables in the same databases when using all 3 servers:  PostgreSQL 10 (5432), PostgreSQL 9.4(local:.s.PGSQL.5432), and PostgreSQL 9.4 (localhost:5432). I cannot change PostgreSQL 10 back to 5434.  There are now 3 servers under “Server Groups” in the PgAdmin 3 Object Browser:

PostgreSQL 10 (localhost:5432)
PostgreSQL 9.4 (local:.PGSQL.5432)
PostgreSQL 9.4 (localhost:5432)

All I really want to do is use the new PostgreSQL 10 (and not PostgreSQL 9.4) with the current databases.  Would like to have just the first PostgreSQL above.  There must be a simple method.  Maybe backup the data under PostgreSQL 9.4, bring it in somehow to PostgreSQL 10, and then delete both 9.4s?

Help would be greatly appreciated, as I have spent an entire day on this am afraid of doing something crazy.

 
David Ellis Rogers
Ranch Hand
Posts: 58
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I just did the following in the terminal:  sudo vi /etc/sysctl.conf
Received the following back:

E325: ATTENTION
Found a swap file by the name "/etc/.sysctl.conf.swp"
         owned by: root   dated: Fri Nov 10 22:18:54 2017
        file name: /private/etc/sysctl.conf
         modified: YES
        user name: root   host name: Davids-MacBook-Pro.local
       process ID: 3176
While opening file "/etc/sysctl.conf"
            dated: Sat Nov 11 18:03:19 2017
     NEWER than swap file!

(1) Another program may be editing the same file.
   If this is the case, be careful not to end up with two
   different instances of the same file when making changes.
   Quit, or continue with caution.

(2) An edit session for this file crashed.
   If this is the case, use ":recover" or "vim -r /etc/sysctl.conf"
   to recover the changes (see ":help recovery").
   If you did this already, delete the swap file "/etc/.sysctl.conf.swp"
   to avoid this message.

Swap file "/etc/.sysctl.conf.swp" already exists!
[O]pen Read-Only, (E)dit anyway, (R)ecover, (D)elete it, (Q)uit, (A)bort:



Could this be a problem; if so, how do I fix it?
I just Quit out of it.
 
Ranch Hand
Posts: 119
Hibernate Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The `.swp` file is created by the vim editor, one of it uses is to prevent multiple instances of vim to edit the file. So this is not the case that cause you the error.
 
David Ellis Rogers
Ranch Hand
Posts: 58
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Should I

recover" or "vim -r /etc/sysctl.conf

?
- probably a minor side issue.

Should I try the backup and restore?
 
Sheriff
Posts: 7113
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This might help.  It involves using pg_dumpall and pg_upgrade.  You need the two versions up and running side by side, so you should be good.
 
David Ellis Rogers
Ranch Hand
Posts: 58
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I deleted PostgreSQL 9.4 (local:.PGSQL.5432).

I thought I was making progress, but met the following problem when I wanted to test out the new RANGE data type.

Using pgAdmin4, which is an icon in the tray, I cannot connect to PostgreSQL 10.  I get the following.  Note that it is looking to connect on port 5434:


I do not have a password for it.

However, if I enter thru the other elephant icon in the tray, pgAdmin3 appears and then both PostrgeSQL 9.4 (localhost:5432), and 10 (localhost:5432) can be opened thru the same port 5432.  I discovered this confusion when I was trying to create a RANGE data type using PostgreSQL 10.  I do not know where to CREATE TYPE x AS RANGE … using the object browser.
My goal is to use the new version 10 datatype tsrange.

Many thanks for help.
 
David Ellis Rogers
Ranch Hand
Posts: 58
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I resolved the problem with 2 ports by using VIM to PostgreSQL.conf to change the port to 5432.

I am left with how to create the RANGE data type.

Noted items:  if I enter thru the elephant icon in the tray, pgAdmin3 appears in the top menu bar next to the Apple and both PostrgeSQL 9.4 (localhost:5432), and 10 (localhost:5432) can be opened thru the same port 5432.

The following PostgreSQL 10 accepted:

with all 3 making the primary key. (The tsrange is in the list of data types.)
This also shows up in PostgreSQL 9.4 and the data is the same as in
10, which the documentation says is correct.

I still do not know how (where) to make the following using the object browser:

I have failed to find various places under PostgreSQL 10 to add in the CREATE TYPE.
And I cannot find anywhere in the documentation how to do it.

How/Where do I create the data type statement?

Many thanks for help.
 
Knute Snortum
Sheriff
Posts: 7113
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think you need pgAdmin 4 to do that.  I looked in pgAdmin III and couldn't find it.
 
David Ellis Rogers
Ranch Hand
Posts: 58
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks, Knute.  I did need pgAdmin 4.
Since I am new to pgAdmin 4, I had a little problem figuring out how to do it (where the stuff is on the pgAdmin 4 windows).  So, here it is for those that haven’t figured it out.

First, in the browser pane, go to your database --> schema -->Types.  Select Create --> Type.  It makes sense to first create your datatype before creating/modifying the table(s) containing the field you want to be of that datatype.

Under the definition column:
Click on the down-arrow within the box that has Composite filled in.  In the drop-down list that appears, select range, then select the subtype.  If necessary, fill in the rest.
Click on Save.

Now create the table:
In the create table dialog that appears, click on the plus sign to the right of Columns to obtain a new row to fill in; fill in the columns.  To select the data type your_type which you just made, you will find it at the end of the Data type pull-down.

See in PostgreSQL 10 documentation:  8.17 Range Types and their construction, 9.19 Range functions and operators, 8.16.6 additional info, 9.45 list of operators, and 9.46 functions on range types; also Table 9.50 and Table 9.51 for complete lists of operators and functions on range types.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic