As said during the office hour, some users will need user databases to store stuff on the postgresql server. On the toolserver, this was possible on request and there only few who needed it.
Not sure if this needs an extra ticket, but for documentation, it's not bad.
Version: unspecified
Severity: normal
I'm the first user with such a request for my WP coords. Please create u_kolossos with read rights for everyone and all rights for me.
Change 123191 had a related patch set uploaded by Alexandros Kosiaris:
Create u_kolossos db and user
I'd like a user (aude) and u_wikidata_geo (or u_aude) so we can have coordinates from wikidata stored and geocoded and available with postgis.
(i did such thing previously for my bot on my personal hosting)
fsainsbu wrote:
I would like user access for u_tasmania or u4582 to the postgis above, on the recommendation of u_kolossos , is this where to ask?
Frank, just to be clear on this, you want a postgis database u_tasmania with read rights for anyone and all rights for a user tasmania ? Because that is what this is for.
fsainsbu wrote:
Ah, my bad, what I thought was it gave me access to postgis db so I can work on kolossos code to generate marks. I think the other bit, ie save coords to mysql/postgis when page saved/updated in wiki may be what Aude is doing? Better wait out while I sort out who is doing what...
I try to access my database in this way:
psql -U kolossos -h labsdb1004.eqiad.wmnet u_kolossos
than I had to insert a password that I don't know.
Ah yes, so to allow for what you requested (your account full privs and other read-only) authentication is indeed required. Do you have GPG keys so I can email you your passwords ?
Thanks, I can access now. But first I try to create a new table on labsdb1005 I got: "ERROR: cannot execute CREATE TABLE in a read-only transaction"
This was the slave where I can't write something. On labsdb1004 it works fine.
I hope you newer switch the master/slaves roles.
Database gis should be readble for every one. As user kolossos I can't access gis database.
FATAL: no pg_hba.conf entry for host "10.68.16.7", user "kolossos", database "gis", SSL off
Is there a role when we should use master and when slave? What happens in emergency case of a server failure? I hope we have not to switch each tool to the running server manually.
Hello Kolossos,
So
It is expected that labsdb1005 is read-only. It is setup in a standby hot slave configuration. Should something bad happen to labsdb1004, we shall have labsdb1005 assume labsdb1004 "personality" (IP, hostname etc) and also switch it to read-write.
Direct usage of labsdb1005 is not really required nor encouraged. As the setup is, it can be used in a load balancing scenario. You can opt to direct read-only queries to it and they will be served normally. Read-write queries should always go to labsdb1004. That being said, I strongly suggest you avoid such a scenario unless you have an actual need for it (and even if you do, don't do it without letting us know).
As far as user kolossos goes, do you really need access to gis database as well?
(In reply to Alexandros Kosiaris from comment #14)
It is expected that labsdb1005 is read-only. It is setup in a standby hot
slave configuration. Should something bad happen to labsdb1004, we shall
have labsdb1005 assume labsdb1004 "personality" (IP, hostname etc) and also
switch it to read-write.
[...]
Could we perhaps use a generic (DNS) alias for that? Having a "real" and a "fake" labsdb1004 seems very confusing; I would expect "hostnames with numbers" to always refer to the same hardware. On Toolserver, there was a similar setup for the MySQL DB servers: "rosemary", "daphne" & Co. always referred to distinct physical hardware, while "sql-s1" & Co. were aliases that pointed to the hosts (actually, to a HA distributor that forwarded the connections to the hosts).
I don't know at the moment if PostgreSQL checks the server's hostname during authentification, though.
I would argue the confusion stems from the "real" and "fake" attributions. There is no "fake" nor "real" labsdb1004. There is a master postgresql server (labsdb1004) and a hot standby (labsdb1005). That means that all applications should use labsdb1004.
labsdb1005 is there so that the operations team can recover the service faster in case of labsdb1004 hardware failure. In which case, apart from the (hopefully minimal) downtime, during which we will be switching over, users and applications should not notice anything and should not change any setting.
The fact that labsdb1005 is capable of answering read-only queries is a conscious design choice, but if it is really causing all this confusion, that can be undone. In fact, just ignoring/forgetting about the existence of labsdb1005 might actually be better for users.
That being said, a separate DNS will indeed provide the ops team with greater flexibility when the unfortunate time of a labsdb1004 hardware failure comes. It will however provide no extra benefit to users. I will look into it to see how we can do it and still be consistent with the rest of Labs.
We require a new postgres database to load public domain shapefiles as part of the Wikimaps Atlas IEG : https://meta.wikimedia.org/wiki/Grants:IEG/Wikimaps_Atlas
dbname: wikimaps_atlas
user: planemad
Change 132813 had a related patch set uploaded by Alexandros Kosiaris:
Create a wikimaps_atlas postgis database
Change 132813 merged by Alexandros Kosiaris:
Create a wikimaps_atlas postgis database
Having requests as additional comments on an eternal bug seems suboptimal. Let's close this one and create new ones for new requests. After the move away from Bugzilla, I assume that Phabricator will let us set up nice forms if these are needed.
Quite true. Thanks Tim. I would like to add that due to some changes in the placement of the OSM databases that freed up some hardware and quite some work from Yuvipanda we hope the creation of postgresql databases per user will be as automatic as for mysql so no new tickets will be required.
Alexandros, I have lost the password to my postgres user account 'planemad'. Can I get a new password?