#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Location
    Brooklyn, NY
    Posts
    14
    Rep Power
    0

    Question Current Server Name for pgadmin3


    Greetings.

    I am a PostgreSQL newbie with great DBA experience in Informix and less in Oracle. I need to come up to speed real quick on PostgreSQL DBA wizardry. I created a database on my Ubuntu machine and have run some queries on the catalogs, mainly pg_class. I wanted to set up pgadmin3 to run on my server. However, the first thing pgadmin3 prompts for is a Server Name in the current Server Group.

    Can you spell Duh?

    How do I get the name of my current server group as well as my current server? My google searches turned up SQL-level function calls like
    and other quite useful tidbits. But no satisfaction on determining the name of my current server instance and server group. So no setting up pgadmin3 for me. Yet.

    Can anyone give me a quick hand with this? Perhaps I just have the wring nomenclature for this. In Informix, you can one server handling unlimited number of database and get the server name by a call to dbinfo('DBSERVERNAME'). Ir Oracle, the database IS the server.

    But I do I determine this in PostgreSQL?

    Thanks much!
    ------------------------
    -- Rasputin Paskudniak
    In pursuit of undomesticated, semi-aquatic avians
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by rpaskudniak
    However, the first thing pgadmin3 prompts for is a Server Name in the current Server Group.
    Only you know that, because you installed Postgres on that server.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Location
    Brooklyn, NY
    Posts
    14
    Rep Power
    0

    Current Server Name for pgadmin3


    Shamat,
    If only that were the case. However, Ubuntu handled that detail - I installed PostgreSQL via Synaptec Package Manager. I can go hunting for a configuration file (postmaster.conf?) and seeking a parameter that looks like it's naming a server but that's a bit much for my level of experience with the product.

    If there is no SQL-level function similar to current_database() for this purpose, in which of [at least] 5 configuration files is it and how do I recognize the server name when I see it? Like, does it read something like:
    Code:
    SERVER_NAME = yargh
    in that file?

    Or shorten the follow-up question: Where and how did I name that server?

    Thanks for the first step there, Shamat.
    ------------------------
    -- Rasputin Paskudniak
    In pursuit of undomesticated, semi-aquatic avians
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by rpaskudniak
    Where and how did I name that server?
    Can you please explain which "server" you exactly mean?

    In the "Add server" dialog there are two input fields: http://www.pgadmin.org/docs/1.14/connect.html

    Are you referring to the "Name" field or the "Host" field?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Location
    Brooklyn, NY
    Posts
    14
    Rep Power
    0

    Current Server Name and Server Group for pgadmin3


    Originally Posted by shammat
    Can you please explain which "server" you exactly mean?

    In the "Add server" dialog there are two input fields: http://www.pgadmin.org/docs/1.14/connect.html

    Are you referring to the "Name" field or the "Host" field?
    Shamat,

    I realize that I have been unclear about this. Thank you for bringing it to my attention. Back to my verbose self.

    With Informix and Oracle (and I'm pretty certain about Sybase as well), I can define and configure several instances of their "postmaster" equivalents. (oninit in the case of Informix, ora<something> in Oracle) Each instance has its own dedicated configuration file(s), its own set of shared memory segments, it's own set of daemons, it's own server name and number. The last two items being set in the main configuration file, along with the other server parameters.

    Picking Informix as my shill: In the case of Informix, as with PostgreSQL, you can have many databases handled by each server. Suppose:
    1. I have one host machine named KAOS.
    2. I have 3 servers defined on KAOS: sigfried, starker, and alexei.
    3. I have a database named payroll on each of these servers.
    4. Each of these has a database named "payroll"

    If I wish to access the payroll database on starker, I address (or CONNECT TO) payroll@starker; that is, <database>@<server_name>

    The fact that I can run initdb many different times with a different -D option each time tells me I can similarly have many PostgreSQL instances running. Each instance is what I mean by the word "server". If PostgreSQL uses a different name for this concept it could explain why my Google searches have been so futile.

    Now let's boil down some essentials of my inquiry:
    1. Where do I set the name of my "server" in each instance I am defining? I believe (possibly incorrectly) that this can be set in one of the many .conf files, if I don't want to use some default server name. Which .conf file and which parameter in that .conf file needs to be edited (or examined)?
    2. If I am in a library function and need to formulate a proper diagnostic message, I can use current_database() to place the database name into my message string. But what would I call to capture the name of the server into that message?

    The answer to either of those questions would tell me what I need to put into the Server Name. (I think.) And, while where at it, I could use a quick explanation of "Server Group". I have chapter 18 [of the 2700+ page complete doc] open in Adobe Reader so I might find it. But I need a quick answer, or even just a page number in that same doc.

    Sorry to be so verbose. I also apologize for some thought patterns left over from previous database experience. But hey, those patterns served me well for many years so I need a very specific GPS (compass?) to point me to new patterns.

    Thank you Shammat et al, for your help with this.
    ------------------------
    -- Rasputin Paskudniak
    In pursuit of undomesticated, semi-aquatic avians
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by rpaskudniak
    Each instance is what I mean by the word "server". If PostgreSQL uses a different name for this concept it could explain why my Google searches have been so futile.
    The name for that is "cluster", which is clearly documented in the manual for initdb.
    Originally Posted by Postgres manual
    initdb creates a new PostgreSQL database cluster. A database cluster is a collection of databases that are managed by a single server instance.
    Taken from: http://www.postgresql.org/docs/current/static/app-initdb.html

    Each "cluster" requires a separate "postmaster" process running on a different port. To access that cluster you specify the host name (of the server) and the port where the postmaster is listening. There is no such thing as a "name" assigned to that.


    Where do I set the name of my "server" in each instance I am defining? I believe (possibly incorrectly) that this can be set in one of the many .conf files, if I don't want to use some default server name.
    There is no "name" assigned to a cluster.
    Btw: what do you mean with "many .conf" files? A single cluster consists of exactly three .conf file. Each one for a well documented part of the system:
    Originally Posted by Postgres Manual
    In addition to the postgresql.conf file already mentioned, PostgreSQL uses two other manually-edited configuration files, which control client authentication
    Taken from: http://www.postgresql.org/docs/9.1/static/runtime-config-file-locations.html


    Which .conf file and which parameter in that .conf file needs to be edited (or examined)?
    The only thing that distinguishes one cluster from another is the port number on which the postmaster process is listening. That port is defined in postgresql.conf.
    Again this is all documented in the manual:http://www.postgresql.org/docs/9.1/static/runtime-config.html


    If I am in a library function and need to formulate a proper diagnostic message, I can use current_database() to place the database name into my message string. But what would I call to capture the name of the server into that message?
    You can't as the port is the only thing that distinguishes them.


    The answer to either of those questions would tell me what I need to put into the Server Name.
    No, the server name is just a name you define on the "client". It can be foobar1 to foobar3. It's only there to distinguish the different entries.
    Originally Posted by pgAdmin Manual
    The name is a text to identify the server in the pgAdmin tree.
    (That's from the link I posted)


    Honestly: I think you just need a single cluster with multiple database. Using several postmaster instances on a single server is a pretty uncommon installation and I wonder why you think you need that.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Location
    Brooklyn, NY
    Posts
    14
    Rep Power
    0

    Current Server Name and Server Group for pgadmin3


    Shammat,

    Thank you enormously!! With but one question left unanswered, you have been enormously helpful in explaining where my thought process has gone astray. And pointing me to the manuals: The only manual I have in my possession is the 2700+page complete US reference, which I could only search in a short interval. So pointing me to the other manuals is a great help as well. The biggest help was that the nomenclature here uses the word "cluster" for what I was calling a "server".

    As to your comment:
    Honestly: I think you just need a single cluster with multiple database. Using several postmaster instances on a single server is a pretty uncommon installation and I wonder why you think you need that.
    The environments I have worked in over the past 12 years all have had multiple instances of their server engines running. One bank I worked at had 11 Informix instances of different release versions on one host alone. (Over 100 hosts, 200+ Informix servers, where Informix was an orphan cousin.) And a great many more Sybase servers per machine. Hence, my mindset of assuming this is the way a huge business would run their databases.

    My unanswered question: I had guessed (I will be looking for it in the manuals!) that I connect to a <database>@<cluster>, where that cluster may reside on a different host. Now that I see this in incorrect, what is the syntax to access a database in a specific cluster? Yes, I'm quite sure the answer is in that monster manual someplace. And I see the issue addressed in chapter 31, Database Connection Control Functions. But how would I do this on the psql command line? Ah, but I just found that answer in the psql Man page:
    Code:
    $ psql --dbname=<database> --host=<host> --port=<port>
    Thanks much for raising my comfort level with these issues and helping me focus on the correct things.

    ------------------------
    -- Rasputin Paskudniak
    In pursuit of undomesticated, semi-aquatic avians
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by rpaskudniak
    And pointing me to the manuals: The only manual I have in my possession is the 2700+page complete US reference, which I could only search in a short interval. So pointing me to the other manuals is a great help as well.
    I wonder what made you miss them on the PostgreSQL home page. The manual is only two clicks away from the start page. It might be interesting for the PostgreSQL team to know why it's so hard to find. Maybe they can move it to a more prominent location then.

    Do make sure you subscribe to the PG mailing lists, there is no better support for Postgres than what you get there:
    http://www.postgresql.org/community/lists/

IMN logo majestic logo threadwatch logo seochat tools logo