Re: [GENERAL] Pg_xlog increase due to postgres crash (disk full)
I had not run these commands on the master as I was only doing sql updates ~1mil of them
Re: [GENERAL] Yet Another Timestamp Question: Time Defaults
On Monday, January 21, 2013 at 18:11, bgd39h5...@sneakemail.com (Nathan Clayton nathanclayton-at-gmail.com |pg-gts/Basic|) wrote: I only wish. I work with a transactional system from the 70s on a daily basis that decided to store something like a "work date" and "work time". The date changes whenever they decide to dateroll the system. Until then the time field continues to grow, so you see times like 25:00 and 26:00 all the time. SELECT execute(relevant_dba) FROM the_70s WITH tardis WHERE working_tardis = true; Exceptions abound. At least that can't be blamed on a government, and, we can only hope ISO-8601 will prevent more examples being created. You sound as though you really need, and/or already have, a dedicated datatype... if only to stop 'the system' from 'fixing' such weirdness. Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running update in chunks?
Jeff Janes wrote: > one hstore field can easily be equivalent to 50 text fields with > an index on each one. > > I'm pretty sure that that is your bottleneck. I agree that seems like the most likely cause. Each update to the row holding the hstore column requires adding new index entries for all the hstore elements, and autovacuum will need to clean up the old ones in the background. The best solution would be to either normalize the data instead of using hstore, or move the hstore to a separate table which is referenced by some sort of ID from the frequently-updated table. -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trouble with Postgresql RPM
On Wednesday, January 23, 2013 09:10:40 AM Ian Harding wrote: > The System: > > Linux beta 2.6.32-279.19.1.el6.x86_64 #1 SMP Wed Dec 19 07:05:20 UTC 2012 > x86_64 x86_64 x86_64 GNU/Linux > That looks like a CentOS 6 system. Go to http://yum.postgresql.org/repopackages.php Find the repo appropriate for your distribution. Download the repo rpm, install that, which will add the repository to your yum configuration. Install PostgreSQL from there. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DB alias ?
Problem: Some users (scripts actually) try to connect to a DB who's name is derived from environmental variables. The DB doesn't exist (yet), and I want them to connect to a different DB for the time being. Is there a way to define an alias for the existing DB that = the db name that doesn't exist?Or is there a way to have PG fail over to a default DB should a DB connect fail? I can implement the fail over outside PG, but those users who make a direct connect to the DB don't use that code. Thanks in Advance
Re: [GENERAL] DB alias ?
Gauthier, Dave wrote: > Problem: Some users (scripts actually) try to connect to a DB who's name is > derived from environmental variables. The DB doesn't exist (yet), and I want > them to connect to a different DB for the time being. Is there a way to > define an alias for the existing DB that = the db name that doesn't exist? > Or is there a way to have PG fail over to a default DB should a DB connect > fail? I can implement the fail over outside PG, but those users who make a > direct connect to the DB don't use that code. This has been requested previously. Probably the only reason it hasn't gotten done yet is that nobody has cooked up a patch. Searching the archives for "database synonyms" might be fruitful. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB alias ?
On 01/23/2013 12:45 PM, Gauthier, Dave wrote: Problem: Some users (scripts actually) try to connect to a DB who's name is derived from environmental variables. The DB doesn't exist (yet), and I want them to connect to a different DB for the time being. Is there a way to define an alias for the existing DB that = the db name that doesn't exist?Or is there a way to have PG fail over to a default DB should a DB connect fail? I can implement the fail over outside PG, but those users who make a direct connect to the DB don't use that code. Pass the database name when you connect? JD Thanks in Advance -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB alias ?
Nope. Think of it this way, a new DB is created on day 1 of every month. So there's a DB called JAN, another called FEB, etc... . The DB name used in the connect is picked up from the current date/time. But January is oevr and I don't want to create the FEB DB until Feb 15th. In the meantime, I want those who try to connect to FEB to connect to JAN (for example). -Original Message- From: Joshua D. Drake [mailto:j...@commandprompt.com] Sent: Wednesday, January 23, 2013 4:04 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] DB alias ? On 01/23/2013 12:45 PM, Gauthier, Dave wrote: > Problem: Some users (scripts actually) try to connect to a DB who's > name is derived from environmental variables. The DB doesn't exist > (yet), and I want them to connect to a different DB for the time being. > Is there a way to define an alias for the existing DB that = the db name > that doesn't exist?Or is there a way to have PG fail over to a > default DB should a DB connect fail? I can implement the fail over > outside PG, but those users who make a direct connect to the DB don't > use that code. Pass the database name when you connect? JD > > Thanks in Advance > -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB alias ?
On 01/23/2013 02:10 PM, Gauthier, Dave wrote: Nope. Think of it this way, a new DB is created on day 1 of every month. So there's a DB called JAN, another called FEB, etc... . The DB name used in the connect is picked up from the current date/time. But January is oevr and I don't want to create the FEB DB until Feb 15th. In the meantime, I want those who try to connect to FEB to connect to JAN (for example). -Original Message- From: Joshua D. Drake [mailto:j...@commandprompt.com] Sent: Wednesday, January 23, 2013 4:04 PM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] DB alias ? On 01/23/2013 12:45 PM, Gauthier, Dave wrote: Problem: Some users (scripts actually) try to connect to a DB who's name is derived from environmental variables. The DB doesn't exist (yet), and I want them to connect to a different DB for the time being. Is there a way to define an alias for the existing DB that = the db name that doesn't exist?Or is there a way to have PG fail over to a default DB should a DB connect fail? I can implement the fail over outside PG, but those users who make a direct connect to the DB don't use that code. Pass the database name when you connect? JD Thanks in Advance -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 alter database JAN rename to FEB; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB alias ?
On 1/23/2013 1:10 PM, Gauthier, Dave wrote: Nope. Think of it this way, a new DB is created on day 1 of every month. So there's a DB called JAN, another called FEB, etc... . The DB name used in the connect is picked up from the current date/time. But January is oevr and I don't want to create the FEB DB until Feb 15th. In the meantime, I want those who try to connect to FEB to connect to JAN (for example). proposed new SQL command: READ USERS MIND; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB alias ?
On 01/23/2013 01:16 PM, Rob Sargent wrote: On 01/23/2013 02:10 PM, Gauthier, Dave wrote: Nope. Think of it this way, a new DB is created on day 1 of every month. So there's a DB called JAN, another called FEB, etc... . The DB name used in the connect is picked up from the current date/time. But January is oevr and I don't want to create the FEB DB until Feb 15th. In the meantime, I want those who try to connect to FEB to connect to JAN (for example). Perhaps it would be better if you more fully explained the problem you are trying to solve (i.e. is it updated data but identical schemas, are you replacing the old or are you keeping the old, etc.). Assuming you have some flexibility in how you solve your actual issue, there are some options. 1. Use pgBouncer so that all users connect to the pooler - perhaps using a standard database like "current" and update the real database to which that connects when it is ready. 2. Use schemas in a database instead of separate databases and update the role information to set the search path to point to the appropriate schema. Perhaps always call the most recent schema "current" then rename schemas as/when needed. 3. Use a connection service file http://www.postgresql.org/docs/current/static/libpq-pgservice.html that is pushed/pulled/shared somehow with updated connection information. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB alias ?
Then someone who wants to look at old JAN data will have the same problem :-( If I recall, Oracle enables something like this. Multiple tnsfilenames (or something like that). There was a connect layer on the server side that the DBA had access to where you could do stuff like this. >> proposed new SQL command: >>READ USERS MIND; :-) Actually, read the DBA's mind. How about... postgres=# create db_alias FEB to db JAN; postgres=# drop db_alias FEB; -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rob Sargent Sent: Wednesday, January 23, 2013 4:16 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] DB alias ? On 01/23/2013 02:10 PM, Gauthier, Dave wrote: > Nope. Think of it this way, a new DB is created on day 1 of every month. So > there's a DB called JAN, another called FEB, etc... . The DB name used in > the connect is picked up from the current date/time. But January is oevr and > I don't want to create the FEB DB until Feb 15th. In the meantime, I want > those who try to connect to FEB to connect to JAN (for example). > > -Original Message- > From: Joshua D. Drake [mailto:j...@commandprompt.com] > Sent: Wednesday, January 23, 2013 4:04 PM > To: Gauthier, Dave > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] DB alias ? > > > On 01/23/2013 12:45 PM, Gauthier, Dave wrote: >> Problem: Some users (scripts actually) try to connect to a DB who's >> name is derived from environmental variables. The DB doesn't exist >> (yet), and I want them to connect to a different DB for the time being. >> Is there a way to define an alias for the existing DB that = the db name >> that doesn't exist?Or is there a way to have PG fail over to a >> default DB should a DB connect fail? I can implement the fail over >> outside PG, but those users who make a direct connect to the DB don't >> use that code. > > Pass the database name when you connect? > > JD > > >> >> Thanks in Advance >> > > > -- > Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL > Support, Training, Professional Services and Development High > Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - > 509-416-6579 > > alter database JAN rename to FEB; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] Question on Trigram GIST indexes
* I think it "should" use that index based on trying to follow that exercise. * The part about changing the collation was an idea in the course of trying out different things. ** enable_seqscan* is off, and the *sharedmem* and *temp_buffers* are set so high that most things happen in RAM. I wonder what it that the other gentleman, Merlin, found out in the documentation and if he would share that. I've also tried this on another table I have, with and without other indexes, but no success :-( Wondering ... On 23 January 2013 04:05, Tom Lane wrote: > ERR ORR writes: > >> Queries which use "WHERE "TST_PAYLOAD" LIKE 'SEAT%'" go to the btree > >> index as it should. > >> Queries which use "WHERE "TST_PAYLOAD" LIKE '%EAT%'" *should* use the > >> GIST index but do a full table scan instead. > > Are you sure it "should" use the index for that? That query doesn't > look very selective to me --- it might well be deciding that a seqscan > is cheaper. You could try forcing the issue with enable_seqscan = off > to see if the query is really unable to match the index, or it just > doesn't like the cost estimate. > > > Would it help to `ALTER DATABASE set lc_collate = 'C'`,supposing that is > > possible? (Oracle doesn't allow that iirc) > > FWIW, I think you do want the index to have the database's default > collation, otherwise it could only match LIKE clauses that explicitly > specify the same non-default collation. > > regards, tom lane >
Re: [GENERAL] DB alias ?
For each phase of a project, a new DB is created. The project phase is
identified in a linux environment variable (lets call it $PHASE). The DB name
that is used in the connect string of the perl/DBI scripts they run is derived
from that in the perl/DBI script, maybe something like this... $db =
$ENV{PHASE}."_DB", followed by the db connect string.
When phase 2 comes along, the DBA would typically create a new DB (P2_DB) so
that the users with their $PHASE set to "P2" would find the correct DB to
connect to. In the meantime, other P1_DB users can still work with the P1_DB
database.
Now phase 3 comes along. Management tells the DBA to NOT create a P#_DB just
yet. They want the P3 users ($PHASE = "P3") to actually work on the P2 DB.
The $PHASE env var cannot be modified as it is used by other tools in the work
environment. The tell the DBA (me) to have them work on the P2_DB database as
if it was the P3_db database. Eventually, they'll tell me to create the P3_DB
database and the problem will go away at that point. But in the meantime, I
have to redirect them to P2_DB without changing anything in the linux
environment. I need to have the DB itself "know" that the dbname "P3_DB"
really = "P2_DB" for the time being. A simple mapping capability could do it.
I'm googling around for the connection service stuff, but its really sparse.
And its not clear where, in my linux install, I'm supposed to find the config
file. Even if I find it, I'll need a utility that the DBA can use to modify it
(I won't have direct access to it for manual edit or anything like that). But
a service file concept sounds intriguing.
Thanks Steve.
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Steve Crawford
Sent: Wednesday, January 23, 2013 4:38 PM
To: Rob Sargent
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] DB alias ?
On 01/23/2013 01:16 PM, Rob Sargent wrote:
> On 01/23/2013 02:10 PM, Gauthier, Dave wrote:
>> Nope. Think of it this way, a new DB is created on day 1 of every
>> month. So there's a DB called JAN, another called FEB, etc... . The
>> DB name used in the connect is picked up from the current date/time.
>> But January is oevr and I don't want to create the FEB DB until Feb
>> 15th. In the meantime, I want those who try to connect to FEB to
>> connect to JAN (for example).
Perhaps it would be better if you more fully explained the problem you are
trying to solve (i.e. is it updated data but identical schemas, are you
replacing the old or are you keeping the old, etc.). Assuming you have some
flexibility in how you solve your actual issue, there are some options.
1. Use pgBouncer so that all users connect to the pooler - perhaps using a
standard database like "current" and update the real database to which that
connects when it is ready.
2. Use schemas in a database instead of separate databases and update the role
information to set the search path to point to the appropriate schema. Perhaps
always call the most recent schema "current" then rename schemas as/when needed.
3. Use a connection service file
http://www.postgresql.org/docs/current/static/libpq-pgservice.html that is
pushed/pulled/shared somehow with updated connection information.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB alias ?
On Wed, Jan 23, 2013 at 10:08:05PM +, Gauthier, Dave wrote:
> For each phase of a project, a new DB is created. The project phase is
> identified in a linux environment variable (lets call it $PHASE). The DB
> name that is used in the connect string of the perl/DBI scripts they run is
> derived from that in the perl/DBI script, maybe something like this... $db =
> $ENV{PHASE}."_DB", followed by the db connect string.
>
It seems that this is your problem. What you need to do is something more like
$db = $ENV{PROJDB}."_DB" || $db = $ENV{PHASE}."_DB"
Then have (only) the people who are supposed to be working on the non-standard
database name set PROJDB in their environment, and your problem is solved. No?
(I have reservations about this entire thing anyway. It feels to me you really
want to be using schemas here, but that's a different discussion.)
Best,
A
--
Andrew Sullivan
a...@crankycanuck.ca
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB alias ?
On 01/23/2013 02:08 PM, Gauthier, Dave wrote: For each phase of a project, a new DB is created But in the meantime, I have to redirect them to P2_DB without changing anything in the linux environment. I need to have the DB itself "know" that the dbname "P3_DB" really = "P2_DB" for the time being. A simple mapping capability could do it. I'm googling around for the connection service stuff, but its really sparse. And its not clear where, in my linux install, I'm supposed to find the config file. Even if I find it, I'll need a utility that the DBA can use to modify it (I won't have direct access to it for manual edit or anything like that). But a service file concept sounds intriguing. First, the convention on this mailing list is to bottom-post so people can follow threads. Top-posting is frowned upon. Given your expanded description I think you should look at pgBouncer. Although it is intended as a connection pooler, the configuration allows you to set a database name on the client-facing side that is different than the actual name of the database the pooler connects to. So both p2_db and p3_db could point to real_p2_db until you update the pgBouncer config. Depending on how you authenticate/authorize you may have to fuss with some of the password settings and to minimize client-side changes you will probably have to change PostgreSQL to listen on a different port then have pgBouncer listen on the standard 5432 so things appear unchanged to the clients. As a bonus, the new databases can be on different machines if you choose. http://pgfoundry.org/projects/pgbouncer/ Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Replacement for Oracle Workspace Manager
Hi, Oracle has a product called Oracle Workspace Manager: http://www.oracle.com/technetwork/database/enterprise-edition/index-087067.html Website says: "Workspace Manager, a feature of Oracle Database, enables application developers and DBAs to manage current, proposed and historical versions of data in the same database. Applications and DBA operations often work with more than one version of the data. Three common reasons to have multiple data versions are concurrency, auditing and scenario creation. Oracle Workspace Manager provides workspaces as a virtual environment to isolate a collection of changes to production data, keep a history of changes to data and create multiple data scenarios for “what if” analysis. It can save money, time and labor over traditional approaches." Does anyone know any approach to replace this product with something in PostgreSQL world? Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Replacement for Oracle Workspace Manager
On 01/23/2013 02:39 PM, Devrim GÜNDÜZ wrote: Hi, Oracle has a product called Oracle Workspace Manager: http://www.oracle.com/technetwork/database/enterprise-edition/index-087067.html Website says: "Workspace Manager, a feature of Oracle Database, enables application developers and DBAs to manage current, proposed and historical versions of data in the same database. Applications and DBA operations often work with more than one version of the data. Three common reasons to have multiple data versions are concurrency, auditing and scenario creation. Oracle Workspace Manager provides workspaces as a virtual environment to isolate a collection of changes to production data, keep a history of changes to data and create multiple data scenarios for “what if” analysis. It can save money, time and labor over traditional approaches." Does anyone know any approach to replace this product with something in PostgreSQL world? You can get the auditing/versioning from tablelog. JD Regards, -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] seeking SQL book recommendation
For a client who needs to learn how to query the db: - No SQL knowledge at all; needs to start from square 1. - Smart, capable person, who will be in this position for a long time, using this db for a long time. - No chance in hell this db will be moved off PG, so PG-centric is fine ;-) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] seeking SQL book recommendation
Scott Ribe wrote on 23.01.2013 23:56: For a client who needs to learn how to query the db: - No SQL knowledge at all; needs to start from square 1. - Smart, capable person, who will be in this position for a long time, using this db for a long time. - No chance in hell this db will be moved off PG, so PG-centric is fine ;-) Not a book, but I like http://sqlzoo.net/ You can switch between different DBMS - including PostgreSQL - when doing the exercises -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] seeking SQL book recommendation
On 1/23/2013 2:56 PM, Scott Ribe wrote: For a client who needs to learn how to query the db: - No SQL knowledge at all; needs to start from square 1. - Smart, capable person, who will be in this position for a long time, using this db for a long time. - No chance in hell this db will be moved off PG, so PG-centric is fine I can't recommend any specific book as I've not read any of them (and been doing SQL too long). But what I *can* recommend is creating a 'sql cookbook', maybe on an internal wiki or blog, with a bunch of sample queries specific to YOUR database schema that solve the sorts of problems your user base including this client are likely to come up against. start with the simplest things and move up to more complex. have a paragraph or two explaining each query, and example output. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] seeking SQL book recommendation
John R Pierce wrote on 24.01.2013 00:19: - No SQL knowledge at all; needs to start from square 1. - Smart, capable person, who will be in this position for a long time, using this db for a long time. - No chance in hell this db will be moved off PG, so PG-centric is fine I can't recommend any specific book as I've not read any of them (and been doing SQL too long). But what I *can* recommend is creating a 'sql cookbook', maybe on an internal wiki or blog, with a bunch of sample queries specific to YOUR database schema that solve the sorts of problems your user base including this client are likely to come up against. start with the simplest things and move up to more complex. have a paragraph or two explaining each query, and example output. Speaking of "cookbook": The DB2 SQL Cookbook is quite nice actually. http://mysite.verizon.net/graeme_birchall/id1.html Even though it's for DB2 most of it (except e.g. the XQuery stuff) can be used for PostgreSQL as well. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] seeking SQL book recommendation
On 01/23/2013 04:19 PM, John R Pierce wrote: On 1/23/2013 2:56 PM, Scott Ribe wrote: For a client who needs to learn how to query the db: - No SQL knowledge at all; needs to start from square 1. - Smart, capable person, who will be in this position for a long time, using this db for a long time. - No chance in hell this db will be moved off PG, so PG-centric is fine I can't recommend any specific book as I've not read any of them (and been doing SQL too long). But what I *can* recommend is creating a 'sql cookbook', maybe on an internal wiki or blog, with a bunch of sample queries specific to YOUR database schema that solve the sorts of problems your user base including this client are likely to come up against. start with the simplest things and move up to more complex. have a paragraph or two explaining each query, and example output. I recommend the company find the cash and time to have this person go to an SQL class. These have proven very helpful when in-house training would put a large dent in an sql-savvy person's productivity. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sending several commands simultaneously to PostgreSQL 8.4
On Monday, January 21, 2013, Alexander Farber wrote: > To make my question more concrete: > if I'd like to round-robin 6 PostgreSQL connections > from my Perl script - how should I change my code: > But what would that accomplish? If your server is constipated on the IO channel, all 6 connections will suffer the same. Cheers, Jeff
Re: [GENERAL] Sending several commands simultaneously to PostgreSQL 8.4
On Monday, January 21, 2013, Alexander Farber wrote: > Hello, > > I run a card game written in Perl on > a CentOS 6.3 / 64 bit + PostgreSQL 8.4.13 > where quite a lot player statistics are written > to the d/b (score, game results, moves, etc.) > > Here a player profile: http://preferans.de/DE11198 > > The machine has a quad intel + 32 GB RAM. > > In poostgresql.conf I set: > > max_connections = 100 > shared_buffers = 4096MB > work_mem = 32MB > log_min_duration_statement = 1 > > I also use pgbouncer (for PHP scripts), > but my Perl game daemon talks > directly to /tmp/.s.PGSQL.5432 > Why not have Perl go through pgbouncer as well? > > My game daemon runs in a non-forking loop > and poll()s TCP sockets to the player machines.. > > Players complain about my server > freezing for few seconds sometimes > and I can see it myself in the game logs - > when data is sometimes written to d/b > (and postmaster processes take 90% CPU). > Any idea what causes that? Your code only seems to do anything with the database at the time that someone logs out. Does everyone log out at the same time? > > So my question is: > > do I have to program a separate daemon - > which would be polled via a Unix domain > socket by my main game daemon and > which would handle sending SQL commands > (typically insert's and select's)? > One alternative possibility would be to use synchronous_commit=off. This opens up the possibility that transactions would be lost in the case of a crash. But you change your code to send off updates and not wait for a response, as you seem to be proposing, then you are also introducing that possibility, just implicitly. Cheers, Jeff
Re: [GENERAL] DB alias ?
On Wednesday, January 23, 2013 09:39:43 PM Gauthier, Dave wrote: > Then someone who wants to look at old JAN data will have the same problem > :-( > > If I recall, Oracle enables something like this. Multiple tnsfilenames (or > something like that). There was a connect layer on the server side that > the DBA had access to where you could do stuff like this. > >> proposed new SQL command: > >>READ USERS MIND; > : > :-) > > Actually, read the DBA's mind. > > How about... > > postgres=# create db_alias FEB to db JAN; > postgres=# drop db_alias FEB; I would have suggested to use pg_services file as documented at http://www.postgresql.org/docs/9.1/static/libpq-pgservice.html http://www.postgresql.org/docs/9.1/static/libpq-connect.html You can think of this as tnsnames replacement. but I am unable to make it work. I don't know what is wrong with this. shridhar@bheem ~$ cat ~/.pg_service.conf [test1] host=localhost dbname=test shridhar@bheem ~$ strace -o psql.strace psql test1 psql: FATAL: database "test1" does not exist shridhar@bheem ~$ grep -i pg_service psql.strace shridhar@bheem ~$ psql test psql (9.2.2) Type "help" for help. test=# \q shridhar@bheem ~$ psql --version psql (PostgreSQL) 9.2.2 -- Regards Shridhar
[GENERAL] Jobs for a Oracle/Postgres DBAs in Australia
I'm hoping this opportunity will be of interest to some of you on this list: LISAsoft [0] has expanded our Australian/New Zealand Open Source Support offerings to include dedicated Postgres commercial support, migrations to Postgres, and training, through our partnership with EnterpriseDB [1]. (LISAsoft is the sole Australian and New Zealand distributor for EnterpriseDB products). If you are a DBA with experience with Postgres and Oracle, and would like to capitalise on this experience by joining a DBA team within an Open Source friendly company, then please have a look at our job descriptions for Sydney [2] and Melbourne [3] in Australia. [0] http://lisasoft.com [1] http://www.enterprisedb.com/ [2] http://www.seek.com.au/Job/oracle-postgres-dba/in/sydney-cbd-inner-west-eastern-suburbs/23849321 [3] http://www.seek.com.au/Job/oracle-postgres-dba/in/melbourne-cbd-inner-suburbs/23849953 -- Cameron Shorter Software and Data Solutions Manager Tel: +61 (0)2 8570 5050 Mob: +61 (0)419 142 254 Think Globally, Fix Locally Geospatial & Data Solutions enhanced with Open Standards and Open Source http://www.lisasoft.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
