Re: [GENERAL] SELECT statement with sub-queries

On Sun, May 28, 2017 at 10:33 PM, Michelle Konzack wrote: > Hello *, > > I try to get columns from my database with a singel SELECT, but I stuck. > > I have 3 tables like: > > > 1) categories (serial,cat) > > 2) manufacturers (serial,m_name) > > 3) products

Re: [GENERAL] all serial type was changed to 1

On Mon, May 1, 2017 at 11:21 AM, Max Wang wrote: > Hi All, > > > > We have a PostgreSQL database. There are 26 tables and we use serial type > as primary key. We had a insert error as “duplicate key value violates > unique constraint, DETAIL: Key (id)=(1) already exists.”

Re: [GENERAL] pg_upgrade error on FreeBSD (9.1 -> 9.5)

On Sat, Jan 21, 2017 at 9:53 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Amitabh Kant <amitabhk...@gmail.com> writes: > > command: "/var/tmp/pgbin.SPOsRj4D/bin/pg_ctl" -w -l > "pg_upgrade_server.log" > > -D "/usr/local/p

[GENERAL] pg_upgrade error on FreeBSD (9.1 -> 9.5)

Hi I am trying to upgrade an exisitng Postgresql cluster from 9.1 to 9.5 on FreeBSD. pg_upgrade fails with the following error: [Verbose Output: http://pastebin.com/YhR8vD03] == $ /usr/local/bin/pg_upgrade -k -d /usr/local/pgsql/data91 -D /usr/local/pgsql/data

Re: [GENERAL] performance tuning postgresql 9.5.5.10 [enterprisedb]

On Fri, Dec 30, 2016 at 12:06 PM, ajmcello wrote: > Reducing worker mem shaved about 12 minutes off the query time.. Thanks > for the suggestion. I lowered it to 10MB instead of 100MB > > [SNIP] > > >>> [postgresql.conf] > >>> max_connections = 10 > >>>

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Tue, Aug 16, 2016 at 1:08 PM, Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > On 29/07/2016 21:06, Larry Rosenman wrote: > >> On 2016-07-29 12:59, Bruce Momjian wrote: >> >>> On Fri, Jul 29, 2016 at 07:49:36PM +0200, Maeldron T. wrote: >>> And yes, I hate upgrading PostgreSQL

[GENERAL] Re: Moving Postgresql 9.1 instance from FreeBSD 9.0 machine to FreeBSD 10.2

On Sun, Dec 20, 2015 at 6:22 PM, Amitabh Kant <amitabhk...@gmail.com> wrote: > Thanks Andreas .. I thought of going the replication way .. But having > done a sample run couple of hours ago, it seems it will work out without > making any changes . Will update this thread o

[GENERAL] Re: Moving Postgresql 9.1 instance from FreeBSD 9.0 machine to FreeBSD 10.2

ndr...@a-kretschmer.de> wrote: > Read the doc about Replikation. I think you can simply set up the New > machine AS streaming Replikation slave and promote it than AS master - with > no downtime. > > Am 20. Dezember 2015 02:50:57 MEZ, schrieb Amitabh Kant < > amitabhk...@gmail.com &g

[GENERAL] Moving Postgresql 9.1 instance from FreeBSD 9.0 machine to FreeBSD 10.2

Hi I have a Postgresql 9.1 instance running on FreeBSD 9.0 (64 bit). The machine is now running out of space and no extra hard disk can be added to the machine. I am planning to move the instance to another machine which is running FreeBSD 10.2 (64 bit) with the same Postgresql version. One idea

[GENERAL] Upgrade postgres cluster on FreeBSD using pg_upgrade

Hi I need to upgrade a PG cluster from 9.1 to 9.4 running on dedicated FreeBSD 9.2 server. Earlier I followed the simple pg_dump / pg_restore, but now that the cluster has grown to around 700 GB (1 TB total HD size), I am now inclined towards using pg_upgrade. pg_upgrade requires both binary to

Re: [GENERAL] Upgrade postgres cluster on FreeBSD using pg_upgrade

/pgsql_94/data to /usr/local/pgsql/data 9. Copy any settings you want to restore and that's it. Above is the basic steps as I remember from memory. But I've successfully upgraded many databases this way. On Sun, Jul 19, 2015 at 1:35 PM, Amitabh Kant amitabhk...@gmail.com wrote: Hi I need

[GENERAL] Recovering database from crashed HD (bad sectors)

Hi A development box hard disk failed which was running a PG instance with multiple databases on it. I got the data recovered with some bad sector errors. Ran another instance of PG (same version), and was to able to take dump (using pg_dump) of all but one database. For one database I am

Re: [GENERAL] Recovering database from crashed HD (bad sectors)

: Amitabh Kant amitabhk...@gmail.com writes: A development box hard disk failed which was running a PG instance with multiple databases on it. I got the data recovered with some bad sector errors. Ran another instance of PG (same version), and was to able to take dump (using pg_dump) of all

Re: [GENERAL] Recovering database from crashed HD (bad sectors)

On Sun, Jul 19, 2015 at 4:10 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Tom Lane wrote: Amitabh Kant amitabhk...@gmail.com writes: As for running the sql command as suggested by Tom, here is the result: template1=# select * from pg_class where pg_relation_filenode(oid) = 11678

Re: [GENERAL] Linux vs FreeBSD

On Fri, Apr 4, 2014 at 9:33 AM, François Beausoleil franc...@teksol.infowrote: Hi all! Does PG perform that much better on FreeBSD? I have some performance issues on a Ubuntu 12.04 which I'd like to resolve. iowait varies a lot, between 5 and 50%. Does FreeBSD better schedule I/O, which

Re: [GENERAL] initdb error

David I just tried installing PG 9.2.2 on FreeBSD 8.3 and initdb went smoothly without any problems. the only difference would be that I had it running in a virtualbox instance. My config were as follows; FreeBSD 8.3-RELEASE-p5 (binary updated through freebsd-update) PG 9.2.2 (installed through

Re: [GENERAL] initdb error

On Fri, Dec 14, 2012 at 4:28 PM, David Noel david.i.n...@gmail.com wrote: Did you use ports to install postgresql? Yes What is the version of postgresql and freebsd you are using? postgresql client and server v. 9.2.2. If all else fails I could try downgrading to a previous version of

Re: [GENERAL] initdb error

On Thu, Dec 13, 2012 at 10:00 PM, Adrian Klaver adrian.kla...@gmail.comwrote: On 12/13/2012 08:18 AM, David Noel wrote: On 12/13/12, Adrian Klaver adrian.kla...@gmail.com wrote: On 12/13/2012 07:38 AM, David Noel wrote: I'm running into the following error message when running initdb

Re: [GENERAL] initdb error

On Thu, Dec 13, 2012 at 10:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: David Noel david.i.n...@gmail.com writes: I've tried initdb directly: initdb -D /zdb/pgsql/data ...and still seem to wind up with the error: creating template1 database in /zdb/pgsql/data/base/1 ... FATAL: could not

Re: [GENERAL] initdb error

On Fri, Dec 14, 2012 at 3:10 AM, David Noel david.i.n...@gmail.com wrote: On 12/13/12, Tom Lane t...@sss.pgh.pa.us wrote: David Noel david.i.n...@gmail.com writes: /zdb is a zfs volume I've created for cvs and postgres. zfs eh? What happens if you point initdb at a non-zfs volume?

Re: [GENERAL] Postgres DB Migration from 8.3 to 9.1

On Fri, Oct 12, 2012 at 7:45 AM, Vishalakshi Navaneethakrishnan nvishalak...@sirahu.com wrote: Hi Friends, We have our production environment database server in Postgres 8.3 version. we have planned to upgrade to lastest version 9.1. Dump from 8.3 and restore in Postgres 9.1 takes more

Re: [GENERAL] PostgreSQL server embedded in NAS firmware?

On Fri, Sep 7, 2012 at 4:10 AM, Andrew Barnham andrew.barn...@gmail.comwrote: Scratch that. An immediate show stopping pitfall occurs to me: the necessity to match CPU/OS Architecture between primary server and replicate target. Doubtful that there are any consumer NAS products out there

Re: [GENERAL] corrupted statistics file pg_stat_tmp/pgstat.stat

I run the same config (FreeBSD 9 with PG 9.1.x) on couple of servers, and they seem to be working fine without any error messages. The only other setting I have in my sysctl.conf is kern.maxfiles . Amitabh On Wed, Aug 15, 2012 at 1:27 PM, Carl von Clausewitz clausewit...@gmail.com wrote: Hi

[GENERAL] Conditional left join

I have the following table structure on Postgres 8.4 : STRUCTURE: tblunit unit_id [integer] unit_location [character varying] DATA: 1,'location1' 2,'location2' 3,'location3' STRUCTURE: tbloperator operator_id [integer] operator_name [character varying] DATA: 1,'operator1'

Re: [GENERAL] Installed. Now what?

On Sun, Nov 20, 2011 at 1:12 PM, Phoenix Kiula phoenix.ki...@gmail.comwrote: snip Another idea. I use CSF/LFD firewall. For TCP_IN, I have enabled 6432 port number. Do I also need to enable it elsewhere, such as TCP_OUT or UDP_IN etc? Could you just try disabling the firewall for once?

Re: [GENERAL] Installed. Now what?

On Sun, Nov 20, 2011 at 4:14 AM, Phoenix Kiula phoenix.ki...@gmail.comwrote: On Sun, Nov 20, 2011 at 3:35 AM, Adrian Klaver adrian.kla...@gmail.com wrote:snip [databases] MYDB = host=127.0.0.1 dbname=MYDB user=MYUSER client_encoding=utf8 port=5432 ;; Configuation section [pgbouncer]

Re: [GENERAL] Installed. Now what?

On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t...@fuzzy.cz wrote: Dne 20.11.2011 04:21, Phoenix Kiula napsal(a): snip My guess is that you actually require a password when connecting to the database, but you haven't specified a password in the pgbouncer.ini file. You have to specify it in

Re: [GENERAL] Installed. Now what?

On Sun, Nov 20, 2011 at 12:02 PM, Phoenix Kiula phoenix.ki...@gmail.comwrote: On Sun, Nov 20, 2011 at 2:16 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Sun, Nov 20, 2011 at 1:57 PM, Amitabh Kant amitabhk...@gmail.com wrote: On Sun, Nov 20, 2011 at 12:12 PM, Tomas Vondra t

[GENERAL] Selecting all records which are in upper case

Hello I have a simple table 'location' : id - Int (associated with a sequence) name - Character varying (100) I have to delete all records where values in name field are all in upper case. For example, if the test data is as follows: idname 1abcc 2Abc dsase 3CDF FDER 4

Re: [GENERAL] Selecting all records which are in upper case

On Sat, Aug 27, 2011 at 3:40 PM, Martijn van Oosterhout klep...@svana.orgwrote: On Sat, Aug 27, 2011 at 03:12:44PM +0530, Amitabh Kant wrote: Hello I have a simple table 'location' : id - Int (associated with a sequence) name - Character varying (100) I have to delete all records

Re: [GENERAL] Postgres on SSD

There have been several discussions for SSD in recent months although not specific to Fusion IO drives. See http://archives.postgresql.org/pgsql-general/2011-04/msg00460.php . You can search the archives for more such reference. Amitabh 2011/8/11 Ondrej Ivanič ondrej.iva...@gmail.com Hi,

Re: [GENERAL] Backup Restore a database in PostgreSQL

Hi Siva Not sure if it would help, but try passing -O in your pg_restore command. Amitabh On Mon, Aug 8, 2011 at 2:04 PM, Siva Palanisamy siv...@hcl.com wrote: Hi John, Thanks a lot for your reply. As usual Backup worked perfectly. When I tried restore using the command you provided, I got

Re: [GENERAL] getting postgres server on freebsd startup?

On Thu, Jun 16, 2011 at 10:59 PM, Jeff Hamann jeff.d.ham...@gmail.comwrote: snip Please don't respond with Why don't you just use the ports collection? There's reasons - like: 1) need to build from source, 3) it's for a tutorial, and 3) postgresql90-server isn't building. Respectfully,

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

On Mon, Jun 20, 2011 at 1:43 AM, Alexander Farber alexander.far...@gmail.com wrote: Hello Cedric and others, On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2011/6/19 Alexander Farber alexander.far...@gmail.com: [pgbouncer] logfile =

Re: [GENERAL] Partitions and indexes

On Fri, Feb 25, 2011 at 1:17 PM, Chetan Suttraway chetan.suttra...@enterprisedb.com wrote: On Wed, Feb 23, 2011 at 12:52 PM, Amitabh Kant amitabhk...@gmail.comwrote: Hello If I have partitioned a table based on a foreign key in a manner where every child table will only have data

Re: [GENERAL] Partitions and indexes

On Sat, Feb 26, 2011 at 11:52 PM, John R Pierce pie...@hogranch.com wrote: On 02/26/11 10:01 AM, Alban Hertroys wrote: On 26 Feb 2011, at 18:04, Amitabh Kant wrote: Now if I partition the table T2 based on field T1id, making sure that each distinct T1id is provided its own child table

Re: [GENERAL] Partitions and indexes

On Sun, Feb 27, 2011 at 12:30 AM, John R Pierce pie...@hogranch.com wrote: On 02/26/11 10:42 AM, Amitabh Kant wrote: On Sat, Feb 26, 2011 at 11:52 PM, John R Pierce pie...@hogranch.commailto: pie...@hogranch.com wrote: On 02/26/11 10:01 AM, Alban Hertroys wrote: On 26 Feb 2011

[GENERAL] Partitions and indexes

Hello If I have partitioned a table based on a foreign key in a manner where every child table will only have data for single value of the foreign key, do I need to create a index for the foreign key in the primary and/or child tables? I am using version 8.4 With regards Amitabh

Re: [GENERAL] error while autovacuuming

On Fri, Aug 27, 2010 at 3:21 PM, tamanna madaan tamanna.ma...@globallogic.com wrote: Hi All Any idea about this problem ?? Thanks.. Tamanna -Original Message- From: tamanna madaan Sent: Fri 8/20/2010 11:54 AM To: pgsql-general@postgresql.org Subject: error while

Re: [GENERAL] InitDB: Bad system call

section as he is running FreeBSD 7.0? Amitabh Kant

Re: [GENERAL] MySQL versus Postgres

://masivakumar.blogspot.com pgtune [http://pgfoundry.org/projects/pgtune/] is already available for this purpose. Amitabh Kant

Re: [GENERAL] db size and VACUUM ANALYZE

/static/sql-vacuum.html With regards Amitabh Kant

[GENERAL] Comparing bit in an integer field

Hello I need to compare the bit values of a integer field in my table. For example, I have a table called t1 with just one field a1 having following values: a1 == 0 12 8 0 1 10 7 19 I am trying to fetch all records where the 3rd binary bit is 1, which from the above example should be 12

Re: [GENERAL] Comparing bit in an integer field

On Mon, Nov 16, 2009 at 7:53 PM, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to Amitabh Kant : test=# select * from t1; a1 0 12 8 0 1 10 7 19 (8 rows) test=*# select a1 from t1 where (a12)::bit = B'1'; a1 12 7 (2 rows) Regards

Re: [GENERAL] Performance evaluation of PostgreSQL's historic releases

On Tue, Sep 29, 2009 at 1:06 PM, György Vilmos vilmos.gyo...@gmail.comwrote: Hello, I've done a benchmark of recent versions of PostgreSQL's last five major releases to see, how performance has changed during the past years from version to version. You can find the article here:

Re: [GENERAL] PostgreSQL versus MySQL for GPS Data

Just to add to this list, I have been using Postgresql to store data for multiple GPS applications handling more than 150-200 vehicles. Some of the tables that I have are running into 20 - 25 million rows at the max, and on average 10 million rows. I am yet to see a problem from the database side,

Re: [GENERAL] PostgreSQL versus MySQL for GPS Data

You would get better results if you posted in mysql forums. http://forums.mysql.com/ Amitabh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general