[GENERAL] copy data from one db into another via copy & psql

Can I copy from one db (via COPY) and pipe the results to a psql/COPY stmt so I can load the data into a table in the second db 'inline' without writing to & reading from a flat file? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: htt

Re: [GENERAL] Table space question

On Friday 02 April 2010 12:44, akp geek wrote: > Hi all - > > I have created dump of a database where I have a tablespace > (data_tblspace ) associated with relations. I have restored to a different > server. I created the same table space. Now I need to create an other > database on t

Re: [GENERAL] How to grant a user read-only access to a database?

On Wednesday 03 March 2010 08:19:46 am Tom Lane wrote: > Kevin Kempter writes: > > I believe all you have to do is this to create a read only user: > > create user ro_user with password 'passwd'; > > alter user ro_user set default_transaction_read_only = true; &

Re: [GENERAL] How to grant a user read-only access to a database?

On Wednesday 03 March 2010 07:29:21 am Merlin Moncure wrote: > On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown wrote: > > As far as I'm aware. It's only in the upcoming version 9.0 that you > > can do things like: > > > > GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; > > > > Other folk on

[GENERAL] playr (or similar tool)?

Hi All; I need a tool that will duplicate ALL messages from one db to another (including begin, commit, etc). I think Playr does (did) this but the myyearbook links from the past pg conference talks (the one from PG East 2009 in particular) no longer work. Anyone know how to get Playr? If i

Re: [GENERAL] Justifying a PG over MySQL approach to a project

On Thursday 17 December 2009 09:51:19 Richard Broersma wrote: > On Thu, Dec 17, 2009 at 8:23 AM, Gauthier, Dave wrote: > > How difficult is it to switch the master's hat from one DB instance to > > another? Let's say the master in a master-slave scenario goes down but > > the slave is fine. Can

Re: [GENERAL] How do I pipe output from query to a file in PostgreSQL?

On Monday 26 October 2009 12:03:13 Penrod, John wrote: > In oracle: > > SPOOL filename.txt > Select * from customer; > SPOOL OFF > > Results are piped to filename.txt > > > How do I do this from the psql command line? > > > John J. Penrod, OCP > Oracle/EnterpriseDB Database Administrator > St

[GENERAL] Data file recovery

Hi all; Here's my scenario: 1) we setup a db on a local server and we created an external tablespace on a san. We loaded about 400GB in the san tablespace. 2) we had lots of HW issues, subsequently the server was re-purposed in an emergency scenario 3) now we want the data back from the san h

[GENERAL] Possible Bug - error creating a tablespace

So I think I *may* have found a bug but I want to be sure before I file a bug. I did a search on the pgsql-bugs list using the search text: cannot create tablespace and got nothing back. Here's the scenario: 1) we installed CentOS 5.3 x86_64 on a 64bit Dell server 2) I installed the pgdg_ce

[GENERAL] print/return only the first X chars of a varchar column?

Hi all; I'm selecting from a table that has a varchar(1000) but I only want to display the firs 20 characters. Looked at the string functions in the docs but nothing jumped out... Suggestions? Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

[GENERAL] indexes on partitioned tables - on the base table, or the partitioned tables?

Hi all; where's the best place for the indexes/constraints on a partitioned table. I assume it's best to place the FK constraints/triggers on the base/master table and the indexes on the individual partition tables. Thoughts? Thanks in advance. -- Sent via pgsql-general mailing list (pgsql-g

[GENERAL] convert epoch to date

Hi all; I know how to convert a date to an epoch: select extract ('epoch' from timestamp '2009-08-12') How do I do the opposite, I want to convert epoch values to a date Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscript

[GENERAL] locking/waiting queries

Hi all ; were seeing a backlog of queries in pg_stat_activity. The system has slowed big time. I see many many queries where waiting = 't' I want to find out for each query which query they are waiting on (who's doing the blocking). What's the best way to find this, I looked at pg_locks for r

Re: [GENERAL] LDAP Configuration for Postgres authenticating against AD

On Tuesday 04 August 2009 19:41:57 Richard Esmonde wrote: > Hi, > > > > I'm new to PostGres (so go easy on my naivety). I am trying to configure > the postgres host based configuration file to permit users to authenticate > against our Active Directory. > > Needless to say both Ubuntu server and A

Re: [GENERAL] where is pg_resetxlog ?

On Saturday 25 July 2009 20:05:14 Scott Marlowe wrote: > On Sat, Jul 25, 2009 at 1:30 PM, Kevin > > Kempter wrote: > > On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote: > >> On Sat, Jul 25, 2009 at 1:08 PM, Kevin > >> > >> Kempter wrote: > >>

Re: [GENERAL] where is pg_resetxlog ?

On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote: > On Sat, Jul 25, 2009 at 1:08 PM, Kevin > > Kempter wrote: > > On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote: > >> On Sat, Jul 25, 2009 at 12:55 PM, Kevin > >> > >> Kempter wrote: > >

Re: [GENERAL] where is pg_resetxlog ?

On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote: > On Sat, Jul 25, 2009 at 12:55 PM, Kevin > > Kempter wrote: > > Hi all; > > > > I'm trying to restore from a tar of the filesystem on a debian box and I > > get xlog errors. > > > > I suspe

[GENERAL] where is pg_resetxlog ?

Hi all; I'm trying to restore from a tar of the filesystem on a debian box and I get xlog errors. I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would I find pg_resetxlog on a debian box that was installed via the deb packages ? or how do I get it ? Thanks in adv

Re: [GENERAL] [Q] single image Table across multiple PG servers

op/etc? > for database-like selects ? It's new - the grid project just moved out of beta i believe. However I know of at least one commercial company using it in production with no issues > > > > > > > > On Tue, 14 Jul 2009 09:45 -0600, "Kevin Kempter&quo

Re: [GENERAL] [Q] single image Table across multiple PG servers

On Tuesday 14 July 2009 13:35:23 you wrote: > yes -- thank you > > that's in the direction of what I am looking for > > > ODBC connectivity and joins across databases! > > > It looks like it cannot support > a single image table across databases It's effectively a single image across nodes - in

Re: [GENERAL] [Q] single image Table across multiple PG servers

On Tuesday 14 July 2009 12:38:27 V S P wrote: > Hi, > thank you for the links > > > I read through the presentation > > and they did not solve the issue for me -- which presenting a e > table from multiple > shards as one single table (at least for reads) for ODBC clients. > > > I also do not think

[GENERAL] Favorite/Recommended ERD tools

Hi All; I'm looking for suggestions per good ERD tools (Linux based preferred). Thoughts? Thanks in advance

Re: [GENERAL] [PERFORM] Postgres Clustering

On Wednesday 27 May 2009 13:33:55 Alan McKay wrote: > > Continuent works (AFAIK) like pgpool clustering, it sends the same > > statements to both/all servers in the cluster but it has no insight to > > the servers beyond this, so if via a direct connection server A becomes > > out of sync with serv

Re: [GENERAL] [PERFORM] Postgres Clustering

On Wednesday 27 May 2009 12:55:51 Eddy Ernesto Baños Fernández wrote: > Try Cybercluster > > -Mensaje original- > De: pgsql-performance-ow...@postgresql.org > [mailto:pgsql-performance-ow...@postgresql.org] En nombre de Alan McKay > Enviado el: miércoles, 27 de mayo de 2009 13:57 > Para

Re: [GENERAL] PGS Tuning Wizard destroys my login

Jenifer; Can you send a copy of both your before and after config files to the list? On Wednesday 08 April 2009 05:16:35 Jennifer Trey wrote: > Look, I am telling now what I did. > > I don't want to hear claims about how its not the tuning wizards fault. It > is! > > And it does more than the

[GENERAL] covering indexes?

Hi all; I saw in the 8.1 release notes that a bug fix for covering index logic was included. Can anyone point me to documentation on how to get PostgreSQL to take advantage of a covering index (i.e. the query will return data only from the index with no need to hit the table) ? Thanks in adva

[GENERAL] Date math question

Hi All; I have a table that contains 2 columns ts (a timestamp) and dursec (a float - number of seconds) I want to insert the following into another table: the ts (timestamp column) and a second date which is ts + dursec I tried these select variations with no luck: select ts, ts + interval d

[GENERAL] Order by question

Hi All; I'm selecting 3 columns. I want to order the results ascending by col1 and col2 and then descending by col3 Whats the syntax for this? Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.

[GENERAL] Date math question

Hi All; I have a table that contains 2 columns ts (a timestamp) and dursec (a float - number of seconds) I want to insert the following into another table: the ts (timestamp column) and a second date which is ts + dursec I tried these select variations with no luck: select ts, ts + interval d

Re: [GENERAL] postgres user account on OSX

On Linux if you install postgres via RPM's and the postgres user account does not exist then the RPM install creates it for you and sets the home dir to the root for the postgres binaries (i.e. /var/lib/ pgsql) Maybe the same thing happens on a Mac install ? On Sep 10, 2008, at 5:14 PM, Da

[GENERAL] Trigger for insert/update of BLOB's ?

Hi List; Can I create an insert/update trigger based on a table that contains lo_* style BLOB's ? Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] function question

Hi LIst; Is there a way to print all the lines being executed for a function, like the equivelant of a psql -ef for an sql file ? Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/

[GENERAL] String concat issues in 8.3.1 (8.3.1 bug?)

I have a table as follows: \d test_dim Table "public.test_dim" Column |Type | Modifiers -+-+ customer_srcid | bigint | not null segment_srcid

[GENERAL] COPY to STDOUT and pipes

Hi List; I want to run a copy (based on a select) to STDOUT and pipe it to a psql copy from STDIN on a different host. here's what I have: 1) a .sql file that looks like this: copy ( select cust_id, cust_name, last_update_dt from sl_cust ) to STDOUT with delimiter '|' This works.

[GENERAL] Date / interval question

Hi List; I'm populating a time dimension. I need to get the number of days since the start of the fiscal year and also the number of months since the start of the fiscal year based on the current 'date' being processed. Example: my current process date is 01/01/2007 start date of fiscal

[GENERAL] Update Join ?

Hi List; Does Postgres allow updates based on the context of a sub-query, something like the sample below ? 1) Insert data (real_tab.keyID and real_tab.data_desc) into a temp table (temp_tab) 2) update real_tab set real_tab.data_desc = temp_tab.data_desc join real_tab on r

[GENERAL] SQL question

Hi List; I have a table that has 3 date columns : create table xyz ( xyz_id integer, date1 timestamp, date2 timestamp, date3 timestamp ) I want to select in a query the xyz_id and the max date column for each row something like : create table temp2 as select xyz_id (max date?) where .

[GENERAL] PostgreSQL and MOLAP ?

Anyone know of any MOLAP/MDDB/MDX Business Intelligence reporting solutions tahat work on top of PostgreSQL ? -- 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] ER Diagram design tools (Linux)

On Wednesday 05 March 2008 20:33:43 Conor McTernan wrote: > I was wondering if anyone knows of any good ER Diagram tools for > Postgres that run on Linux. > > I have been using DBDesigner by FabForce for a couple of years, but > development has stopped while MySQL workbench is being built (for > wi

Re: [GENERAL] How to view temp tables

On Thursday 21 February 2008 00:17:56 Chris wrote: > pc wrote: > > My php code is creating temporary table named mytemp,but when I run a > > selec * from mytemp I cannot see the table.How can I see the table > > from postgresql command prompt? > > temp tables are automatically deleted when the conn

[GENERAL] temp table question

Hi list; If I create a temp table (i.e. create temp table xyz as select from ...) is the scope of this table limited to a session. Meaning, can several sessions all run the above create temp table statement all referencing the same temp table name at the same time? Thanks in advance ---

[GENERAL] How to query for a user-table column name?

Hi List; I'm researching a db and I want to find samples of some of the data. I know based on the documentation for the proposed "new" schema that the db I have access to (the "old" schema) probably has a column in one or more of the user tables called 'region'. I'm looking for a way to query

Re: [GENERAL] thank you

On Tuesday 18 December 2007 19:26:40 Kevin Hunter wrote: > Hullo List, > > This is aimed at everyone in this community who contributes to the > Postgres project, but especially at the core folks who continually make > this community great through energy, time, money, responses, and > what-have-you.

[GENERAL] is the default of lowercase enforced on all OS platforms?

Hi List; I know that the default case for postgres (psql) on Linux/*ix platforms is lowercase. I wonder is this also true on Windows platforms? Thanks in advance.. /Kevin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ?

[GENERAL] A few basic troubleshooting questions

Hi List; I have a few basic troubleshooting questions... 1) If I have autovacuum turned on, how do I know which table is being vacuumed when in pg_stat_activity I only see VACUUM? I've been using this query but it doesn't always work... is there a better way? CREATE Temp table tmp_p as SELECT

Re: [GENERAL] One database vs. hundreds?

On Tuesday 28 August 2007 06:32:32 A. Kretschmer wrote: > am Tue, dem 28.08.2007, um 14:23:00 +0200 mailte Kamil Srot folgendes: > > Kynn Jones wrote: > > >I'm hoping to get some advice on a design question I'm grappling with. > > > I have a database now that in many respects may be regarded as an

Re: [GENERAL] SQL Diff ?

On Saturday 25 August 2007 23:49:39 Ron Johnson wrote: > On 08/25/07 22:21, Kevin Kempter wrote: > > On Saturday 25 August 2007 21:10:19 Ron Johnson wrote: > >> On 08/25/07 21:51, Kevin Kempter wrote: > >>> Hi List; > >>> > >>> I have a very la

Re: [GENERAL] SQL Diff ?

On Saturday 25 August 2007 21:10:19 Ron Johnson wrote: > On 08/25/07 21:51, Kevin Kempter wrote: > > Hi List; > > > > I have a very large table (52million rows) - I'm creating a copy of it to > > rid it of 35G worth of dead space, then I'll do a sync, drop the

[GENERAL] SQL Diff ?

Hi List; I have a very large table (52million rows) - I'm creating a copy of it to rid it of 35G worth of dead space, then I'll do a sync, drop the original table and rename table2. Once I have the table2 as a copy of table1 what's the best way to select all rows that have been changed, modifi

[GENERAL] DRDB risk factors?

Hi List ; per considering DRDB as a replication solution in a failed master node scenario, is there a risk of loosing not only in-flight transactions but alos un-sync'd buffer-pool dirty pages? If so, how might I minimize this risk ? Thanks in advance... ---(end of bro

[GENERAL] pgpool redundancy question

Hi List; It seems that in a replication scenario pgpool becomes the single point of failure, i.e. if we loose the pgpool box were dead. Any thoughts, suggestions, best practices, etc per creating redundancy in the pgpool layer? I've posted to the pgpool list as well but I wanted to get some tho

[GENERAL] STDERR vs. SYSLOG logging

Hi List; Anyone have any thoughts per which logging method (SYSLOG vs STDERR) is the better approach ? Thanks in advance... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] Or selection on index versus union

Just a guess but have you tried using an in clause? select something from table where fase in ('1' , '2'); On Tuesday 04 October 2005 13:32, [EMAIL PROTECTED] wrote: > Hello > > I've got a table with an index, let's call it fase. > > The following query is fine: 'select something from table w