Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2014
    Posts
    4

    Unanswered: why does data replicate to other servers

    I am very new to this.

    SUMMARY: My Postgres is deleting what's in the other servers and making all servers have identical databases, tables and roles.

    The term "server" seems to be used for more than one thing. By "server" I'm talking about the thing you create anew with the top left pluglike icon in PgAdmin, so that you aren't doing your work in the default server, which in my case is called "PostgreSQL 9.4 (localhost:5432)".

    I have had no problem making tables etc. in PgAdmin or psql.

    But I just noticed that when I create a table or a role in a server, it creates the same thing in the other servers, and deletes what was already in those servers.

    It seems that a different server should have no automatic connection to the other servers. For example, if I had a web app that people could subscribe to, each would be assigned his own server corresponding to his user id, and in his server he could make his own databases. Or so I assumed. Otherwise, what is the purpose of creating new servers?

    I uninstalled 9.3.5, thinking I might have broken it when I was one day old in this. The new installation of 9.4.0 remembered the old server names but they were empty, having only postgres superuser and only postgres default db. I dropped all these servers and created a new one. Made a db, a table, columns, and a row. The same thing happened, now there is duplicate info in the default server as what I put into the server I created.

    Is there something I am missing about the basic architecture of how databases are supposed to work? How could I have different subscribers working on different projects if their projects copy to each other and delete each other?

    The only error I saw when installing 9.4 (and 9.3 before that) was when I connect with psql I get this: "WARNING: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details."

    I tried the advice of the documentation and the error message didn't go away. Saw advice online to ignore this warning.

    Should I be using MySql since I am so new and have no background in databases? At this point I would be happy to switch if it would mean not spending so much time searching for answers. PG might be the best, but their documentation is like experts talking to experts, and they seem to have skipped over very basic stuff for newbies like "what is a server" and what these "servers" are for. Also I don't know the difference between a "server" and a "cluster".

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    It seems you are confusing pgAdmins "Server" with a "real" database server.

    pgAdmins wording is - mildly put - confusing.

    When you define a "server" in pgAdmin it is essentially just a simple connection definition to a running Postgres instance (which is also referred to as a "server process").

    If you replace the word "Server" with "Connection" in pgAdmin's "Object browser" then (I think) things are less confusing.

    So in pgAdmin you essentially don't have different servers, you have different connection definitions. pgAdmin also calls this a "server registration": you register the database server with the pgAdmin tool (other tools call it e.g. a "connection profile").

    In your case all those server registrations apparently point to the same Postgres server: your installation on localhost.

    If you only have a single database (and only one Postgres installation on your computer) and you only use a single user, then you only need to define a single "Server" in pgAdmin.

    Also I don't know the difference between a "server" and a "cluster".
    The term "server" is used for referring a running installation of Postgres. Typically it can refer to the software and the hardware (a DBMS is a "server process" than runs on a "database server" - the actual computer hardware).

    A cluster - Postgres terms - is nothing but the collection of files that are managed by a running server process:

    Quote Originally Posted by The fine manual
    A database cluster is a collection of databases that are managed by a single server instance.
    Taken from: http://www.postgresql.org/docs/curre...pp-initdb.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Dec 2014
    Posts
    4
    Quote Originally Posted by shammat View Post
    In your case all those server registrations apparently point to the same Postgres server: your installation on localhost.

    If you only have a single database (and only one Postgres installation on your computer) and you only use a single user, then you only need to define a single "Server" in pgAdmin.
    So the program is working correctly. The server registrations are different connections to the same set of databases but I only need to add one.

    This works for me now at home on my own computer. I have Apache web "server" (that word again) installed as well as PHP so I can learn to make dynamic web pages that update a database that's on a remote server.

    Ultimately, however, I would need to know what I have to do differently in order to use a reseller hosting account where users who rent space from me access a cloned template of a db which they then populate with their own data, and which will never communicate with another user's db. How would I emulate this situation on my own computer? Install more copies of Postgres which use something other than localhost? I didn't know this was possible. If it can't be done at home, I do have the website already so could do it there instead.

    Sounds like I need to research what "localhost" actually is. I thought it was the only way to install Pg.

    Thanks for your reply, and I will hopefully be better equipped to research what to do and how to do it.

    The SQL is the easy part.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Jedster View Post
    I have Apache web "server" (that word again)
    In IT/computer terminology "server" is a pretty broad term. It refers to something that offers a service - it "serves" something. In case of the Apache Web Server it "serves" HTML pages. A database server "serves" data, a mail server "serves" emails.

    The term can refer to a piece of software or the actual hardware. A single "server computer" can run many "server programs" (database, mail, web, ...). Each "server program" gets used by many "clients". In case of a database server it's a SQL client (e.g. psql, pgAdmin). In case of a Web Server it's the browser (e.g. Firefox) and in case of an email server it's an email client (e.g. Thunderbird). One server can be the other server's client. This is the case for a PHP program. The PHP program runs on the Apache Web Server and connects to a database server - acting as a client to the database.

    Sounds like I need to research what "localhost" actually is. I thought it was the only way to install Pg.
    "localhost" is just an alias name for the computer where a piece of software is currently running. Every computer has a "name" that identifies it in a network (this can be a private network or a public network). In order for "Program A" to be able to "speak" to "Program B" running on the same computer without the need to know the computer's real name it uses "localhost" (which means "this computer").

    Once you have a remote computer (=server) that runs Postgres you need to register that server within pgAdmin in order to be able to connect to it.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Dec 2014
    Posts
    4

    multiple instances will run on home PC?

    Quote Originally Posted by shammat View Post
    Once you have a remote computer (=server) that runs Postgres you need to register that server within pgAdmin in order to be able to connect to it.
    Thanks for the clarification.

    So, when the user creates a new database, the remote server generates a whole new installation of Postgres, a separate "cluster". That's why the separate user accounts don't see or effect each other.

    As for now, while I'm just working with Apache as my web server on my own computer at home, is there a way to pretend I'm another user by calling the computer something else instead of "localhost"? If so, would I have to register this name with Apache too?

    I have a lot of research to do.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Jedster View Post
    So, when the user creates a new database, the remote server generates a whole new installation of Postgres
    No, not at all. One "cluster" contains multiple datatabase.

    You need to have a running Postgres intallation in order to be able to run "CREATE DATABASE".

    Creating the database cluster (including an initial database) is part of the setup process of a Postgres server.

    As for now, while I'm just working with Apache as my web server on my own computer at home, is there a way to pretend I'm another user by calling the computer something else instead of "localhost"? If so, would I have to register this name with Apache too?
    You are completely confusing "user" and "computer" here.

    There are several levels of users in your system: the operating system user to use to log in. Then there are typically multiple database users that are defined and managed by the DBMS (they are independent of the operating system user). And finally you have a users the application you create.

    To pretend to be "a different user" you need to decide if this should be on the operating system level, the database level or your application level.

    The computer that runs the application, the web server and the database server is not identified by a "user" but only by the computer's name (or IP address). You don't pretend to be "another user" by calling the computer differently. Your computer already has a different name than "localhost": the name under which it is visible in your (local) network.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Dec 2014
    Posts
    4

    web app users aren't postgres users, don't access db?

    Quote Originally Posted by shammat View Post
    No, not at all. One "cluster" contains multiple datatabase.

    You need to have a running Postgres intallation in order to be able to run "CREATE DATABASE"....You are completely confusing "user" and "computer" here.
    I opened psql and created two login roles, jed and mel. As superuser I created two databases, one owned by jed and one owned by mel. I logged in as jed and found that I could make a table in mel's db. Bummer.

    Then -- I think -- I realized what you were trying to tell me.

    My front end users are users of my web app. They are not the same thing as the pg roles.

    I have a new customer Hal. He opens a new account on my web app, he gets a password. When he pushes a button "make new database" in the web app, it works.

    But my other customer, Fred, does not see Hal's db and vice versa. They don't know each other's databases exist, so they are unable to change any db except their own.

    But I don't see why the front-end user names shouldn't each correspond with a pg role. I do realize they are not the same thing. The front end user is created in the web app. I have to put code on a button. When the new account details are verified and all is OK, the button is pushed so php tells postgres to make a database role for that person, let him login, let him make databases, give him a password (?didn't I already give him one to access his web app user account?), and create roles in pg so he can let his friends work on his db too.

    My web app users are NOT database roles or users, either one. My WEB APP changes all users' databases using php commands I have programmed into it. My web app customer uses the web app. The web app uses the database. My subscriber's password doesn't get him into postgres, it just activates his web app buttons. So I guess I need a way of generating passwords in my web app, because the web app user doesn't access postgresql, so he doesn't need a password for pg.

    This should explain what would keep Hal the hacker from guessing Fred's db name and making changes to Fred's db. Hal the hacker, even though he's a subscriber to the web app, could guess the exact url of every page on Fred's subscriber acct to the web app, but that doesn't tell him how to access anything on postgresql. Only the php knows how to do that.

    Am I getting closer?

    This is all done on one installation of PostgreSQL, all can be mocked-up by me on my home computer pretending to be different web app subscribers on one Apache web server connection called "localhost". Right?

    And if so, then at the remote computer (the host for my reseller account), when my subscriber gets to make his own website based on my templates, the remote server does not have to install a fresh copy of PostgreSQL just for him to use. One installation of PG runs any number of databases, and the different database roles and/or users can only do what they are told by the php, while the web app users are isolated from the actual postgresql program itself by the web app.

    Reflecting back on what started me on this thread: pg login roles can work on any db. But changes to one database don't affect another database. So my web app subscribers are isolated from each other by php commands that only change one database at a time, and the php coding is not going to make changes to any database not listed in this subscriber's web app account.

    Still, with Hal the hacker wanting to do mischief, I think it would be good if the subscriber's web app account number was ciphered into a dissimilar pg role name when the new account code is submitted by php to the remote server.

    One purpose of having separate roles for each web app user is so changes made could be traced to the account that originated them.

    How close am I to understanding the situation?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •