[GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x The origin database "data/base" directory is 197 GB in size. The slave database "data/base" directory is 562 GB in size and is over 75% filesystem utilization which has set off the "disk free" siren. My biggest table* measures 154 GB on the origin, and 533 GB on the slave. (*As reported by SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid)) As "Size" from pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; ) I took a peek at this table on the slave using pgadmin3. The table has auto-vacuum enabled, and TOAST autovacuum enabled. There are 8.6 million live tuples, and 1.5 million dead tuples. Last autovacuum was over a month ago. Last autoanalyze was 3 hours ago. Table size is 4 Gigs, and TOAST table size is 527 Gigs. Indexes size is 3 Gigs. Autovacuum threshold is 20%, and the table is just under that threshold. I ran vacuum analyze verbose. But the filesystem is still at 76% utilization. In fact, now, the "data/base" directory has grown to 565 GB. Why is my slave bigger than my master? How can I compact it, please? Best, Aleksey -- 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] Single server multiple databases - extension
That solved the issue. Apart from hstore, I needed to drop ghstore as well. Thanks again From: Tom Lane To: Brian Trudal Cc: Bartosz Dmytrak ; "pgsql-general@postgresql.org" Sent: Tuesday, March 6, 2012 4:09 PM Subject: Re: [GENERAL] Single server multiple databases - extension Brian Trudal writes: > Thanks for getting back to me. Still no luck; and I tried all possibilities.. > For example, when I tried on new DB: > db1=# CREATE EXTENSION hstore > SCHEMA public > VERSION "1.0"; > ERROR: type "hstore" already exists > db1=# create table foo(id hstore); > ERROR: type "hstore" is only a shell Apparently you've got a shell type named "hstore" cluttering that database. Try "DROP TYPE hstore" and then see if you can create the extension. regards, tom lane -- 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] corrupted table postgresql 8.3
- Original Message - > On 6.3.2012 21:24, Matteo Sgalaberni wrote: > > Hi people! > > > > I have a pg 8.3. Today I issued in a database that comand: > > Which minor version? The last one in this branch is 8.3.18 and if > you're > running an old one, there might be an important bugfix ... 8.3.0, I read quickly all releases notes of 8.3.x and I didn't saw any fix that could be directly related to what is happened to me...there are issues related to ALTER TABLE but with other options like PRIMARY KEY or other parameters. Are there fixes that could be related to what's happened to me? > Not sure what you mean by 'physical data file of the cluster' but you > should do a file-level backup of the whole cluster right now. Before > trying to fix the issues (possibly damaging the data). > I copied the file of the filesystem that contain the table. (grep exampledata * and found the $file that contain the table data). On another server i did this: CREATE TABLE cliente... SELECT oid,relname from pg_class where relname = 'cliente'; cp $file $oid cp $production_pg_clog local/pg_clog/ SELECT id,etc from table into tmp_table; all my recovered data was into tmp_table dumped the tmp_table and copied to the production server > What do you mean by 'populated the table' with the production data? > How > did you do that? on the production server: ALTER TABLE cliente RENAME TO cliente_prova; ALTER TABLE DROP CONSTRAINT etc (removed all foreign key that are pointing to cliente) CREATE TABLE cliente psql database < dumpof_tmp_table.sql So I recovered the table cliente. Now, if I try to drop the cliente_prova I receive the error posted in the previous email, and if I type the \d cliente on psql, it return the schema twice of the table "cliente"... For cleanup the situation I think that I need to remove the "old table" and repair the catalog that seems to be damaged... What do you suggest me? Thanks! Matteo -- 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] replication between US <-> EU
Hi, On 7 March 2012 10:36, John R Pierce wrote: > On 03/06/12 3:31 PM, Ondrej Ivanič wrote: >> >> - one side completely down: Client should use switch to other side >> transparently (Failover / High Availability) > > > what happens if the link between the sites is down and both sides decide > they are master? then how do you put the pieces back together ? Good catch! From application point of it is quite easy to sync data between sites. Let's assume that writes should be disabled in this scenario. -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- 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] Single server multiple databases - extension
Brian Trudal writes: > Thanks for getting back to me. Still no luck; and I tried all possibilities.. > For example, when I tried on new DB: > db1=# CREATE EXTENSION hstore > SCHEMA public > VERSION "1.0"; > ERROR: type "hstore" already exists > db1=# create table foo(id hstore); > ERROR: type "hstore" is only a shell Apparently you've got a shell type named "hstore" cluttering that database. Try "DROP TYPE hstore" and then see if you can create the extension. regards, tom lane -- 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] Single server multiple databases - extension
Thanks for getting back to me. Still no luck; and I tried all possibilities..
For example, when I tried on new DB:
db1=# CREATE EXTENSION hstore
SCHEMA public
VERSION "1.0";
ERROR: type "hstore" already exists
db1=# create table foo(id hstore);
ERROR: type "hstore" is only a shell
LINE 1: create table foo(id hstore);
^
db1=# create table foo(id public.hstore);
ERROR: type "public.hstore" is only a shell
LINE 1: create table foo(id public.hstore);
db1=# SELECT * FROM pg_available_extension_versions
engine_db-# WHERE name = 'hstore';
-[ RECORD 1 ]-
name | hstore
version | 1.0
installed | f
superuser | t
relocatable | t
schema |
requires |
comment | data type for storing sets of (key, value) pairs
db1=# SELECT * FROM
pg_extension e INNER JOIN pg_namespace n ON (e.extnamespace = n.oid);
-[ RECORD 1 ]--+---
extname | plpgsql
extowner | 10
extnamespace | 11
extrelocatable | f
extversion | 1.0
extconfig |
extcondition |
nspname | pg_catalog
nspowner | 10
nspacl | {postgres=UC/postgres,=U/postgres}
But if I use it in other DB, where it was installed; it works fine..
db2=# SELECT * FROM
pg_extension e INNER JOIN pg_namespace n ON (e.extnamespace = n.oid);
-[ RECORD 1 ]--+---
extname | plpgsql
extowner | 10
extnamespace | 11
extrelocatable | f
extversion | 1.0
extconfig |
extcondition |
nspname | pg_catalog
nspowner | 10
nspacl | {postgres=UC/postgres,=U/postgres}
-[ RECORD 2 ]--+---
extname | hstore
extowner | 10
extnamespace | 2200
extrelocatable | t
extversion | 1.0
extconfig |
extcondition |
nspname | public
nspowner | 10
nspacl | {postgres=UC/postgres,=U/postgres}
db2 =# SELECT * FROM pg_available_extension_versions
WHERE name = 'hstore';
-[ RECORD 1 ]-
name | hstore
version | 1.0
installed | t
superuser | t
relocatable | t
schema |
requires |
comment | data type for storing sets of (key, value) pairs
any other hints ?
From: Bartosz Dmytrak
To: Brian Trudal
Cc: "pgsql-general@postgresql.org"
Sent: Tuesday, March 6, 2012 1:02 PM
Subject: Re: [GENERAL] Single server multiple databases - extension
Hi,
there shouldn't be any problem in installing extensions to multiple databases
in the same server. Extensions are per
database: http://www.postgresql.org/docs/9.1/static/sql-createextension.html
You can use pgAdmin, or try this syntax:
CREATE EXTENSION hstore
SCHEMA public
VERSION "1.0";
if hstore is installed in public schema, sometimes You have to use
public.hstore syntax (fully qualified name) - this depends on your search_path
setting.
in your example it looks like hstore is installed, but question is: where is it?
You can find this info using SQL like this one:
SELECT * FROM
pg_extension e INNER JOIN pg_namespace n ON (e.extnamespace = n.oid)
http://www.postgresql.org/docs/9.1/static/catalog-pg-extension.html
this could be useful too:
SELECT * FROM pg_available_extension_versions
WHERE name = 'hstore'
http://www.postgresql.org/docs/9.1/static/view-pg-available-extension-versions.html
Regards,
Bartek
2012/3/6 Brian Trudal
Any one know how to install extensions to multiple databases in the same server
?
>
>Thanks in advance
>Brian
>
>
>
> From: Brian Trudal
>To: "pgsql-general@postgresql.org"
>Sent: Monday, March 5, 2012 4:52 PM
>Subject: Single server multiple databases - extension
>
>
>
>Hi
>
>I have 2 databases running in a single server; and I installed extension
>'hstore' to one database and it works fine. When I tried to use the same
>extension in another database, it gives an error saying 'extension does not
>exist'; nor it allow to install as it complains about its existence.
>
>Any help ?
>
>db1=# CREATE EXTENSION hstore;
>ERROR: type "hstore" already exists
>db1=# DROP EXTENSION hstore;
>ERROR: extension "hstore" does not exist
>db1=# create table foo(id hstore);
>ERROR: type "hstore" is only a shell
>LINE 1: create table foo(id hstore);
> ^
>
>
>
Re: [GENERAL] replication between US <-> EU
On 03/06/12 3:31 PM, Ondrej Ivanič wrote: - one side completely down: Client should use switch to other side transparently (Failover / High Availability) what happens if the link between the sites is down and both sides decide they are master? then how do you put the pieces back together ? -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] replication between US <-> EU
Hi, I would like to get some ideas about subject. I do not have any preferred solution (hot-standby, Slony or pgpoll) so anything which can deliver/satisfy the following will good: - one side completely down: Client should use switch to other side transparently (Failover / High Availability) - database writes: transparent for application ie. connection pooler/... should redirect writes to master (it would be nice to have writes on both sides and everything in sync) and use local database for reads (database holds metadata/configuration; just few writes) - replication lag: Clients shouldn't use "stalled data". Switch to master or stop and wait are preferred actions. - automatic failover w/o DBA intervention (or minimal intervention) The other things to consider are: - average latency could be up to 400ms. There is no problem to buy connectivity from different provider if we can use simpler/more robust setup. But I don't know which provider can deliver low latency link suitable for this purpose. - one second lag between master and slave would be tolerable but if we can go lower that would be nice (low latency link, configuration, ...) but we need robustness - kernel or tcp/ip tweaks? We use CentOS 5.7 - database is small; around 1.5GB. It doubles in size every 6..9 months. - NetScaler like appliance could be used Thanks! -- Ondrej Ivanic (ondrej.iva...@gmail.com) -- 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] Complex transactions without using plPgSQL Functions. It is possible?
On Wed, Mar 7, 2012 at 9:25 AM, Tom Lane wrote: > In psql, see "\set AUTOCOMMIT off". In other frontends, it would depend > on the client-side code whether or how you can do that. > > (We once made an attempt to provide this sort of behavioral option on > the server side; but it was a complete disaster from the client > compatibility standpoint, and was soon, um, rolled back.) I'm talking about using the application-level protocols (eg the pg_* functions in PHP), so it's your second option. And yeah, I see your point. Major compat issues. Well, at least the libpqxx library can solve that one! ChrisA -- 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] Unable to write inside TEMP environment variable path
there's another reason for this message. I solve this problem by installing postgre in folder with name that have no spaces, such as C:\PostgreSQL -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unable-to-write-inside-TEMP-environment-variable-path-tp3315027p5542027.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] Complex transactions without using plPgSQL Functions. It is possible?
Chris Angelico writes: > As a side point: Is it possible to disable Postgres's default > autocommit behavior and have it automatically open a transaction on > connection and after commit/rollback? That's what I grew up on with > DB2 - you do some work, then you commit, then you do more work, then > you commit, but never have to say "and begin a transaction too". In psql, see "\set AUTOCOMMIT off". In other frontends, it would depend on the client-side code whether or how you can do that. (We once made an attempt to provide this sort of behavioral option on the server side; but it was a complete disaster from the client compatibility standpoint, and was soon, um, rolled back.) regards, tom lane -- 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] Single server multiple databases - extension
Hi, there shouldn't be any problem in installing extensions to multiple databases in the same server. Extensions are per database: http://www.postgresql.org/docs/9.1/static/sql-createextension.html You can use pgAdmin, or try this syntax: CREATE EXTENSION hstore SCHEMA public VERSION "1.0"; if hstore is installed in public schema, sometimes You have to use public.hstore syntax (fully qualified name) - this depends on your search_path setting. in your example it looks like hstore is installed, but question is: where is it? You can find this info using SQL like this one: SELECT * FROM pg_extension e INNER JOIN pg_namespace n ON (e.extnamespace = n.oid) http://www.postgresql.org/docs/9.1/static/catalog-pg-extension.html this could be useful too: SELECT * FROM pg_available_extension_versions WHERE name = 'hstore' http://www.postgresql.org/docs/9.1/static/view-pg-available-extension-versions.html Regards, Bartek 2012/3/6 Brian Trudal > Any one know how to install extensions to multiple databases in the same > server ? > > Thanks in advance > Brian > -- > *From:* Brian Trudal > *To:* "pgsql-general@postgresql.org" > *Sent:* Monday, March 5, 2012 4:52 PM > *Subject:* Single server multiple databases - extension > > Hi > > I have 2 databases running in a single server; and I installed extension > 'hstore' to one database and it works fine. When I tried to use the same > extension in another database, it gives an error saying 'extension does not > exist'; nor it allow to install as it complains about its existence. > > Any help ? > > db1=# CREATE EXTENSION hstore; > ERROR: type "hstore" already exists > db1=# DROP EXTENSION hstore; > ERROR: extension "hstore" does not exist > db1=# create table foo(id hstore); > ERROR: type "hstore" is only a shell > LINE 1: create table foo(id hstore); > ^ > > >
Re: [GENERAL] corrupted table postgresql 8.3
On 6.3.2012 21:24, Matteo Sgalaberni wrote: > Hi people! > > I have a pg 8.3. Today I issued in a database that comand: Which minor version? The last one in this branch is 8.3.18 and if you're running an old one, there might be an important bugfix ... > =# ALTER TABLE cliente ADD COLUMN pwd_expired boolean DEFAULT FALSE; > WARNING: unexpected attrdef record found for attr 22 of rel cliente > WARNING: unexpected attrdef record found for attr 22 of rel cliente > WARNING: unexpected attrdef record found for attr 22 of rel cliente > ALTER TABLE > Time: 1184.404 ms > > After that the table was empty. > > SELECT * from cliente; > 0 rows ;) > Should contain about 90k records. > > I checked in the logs and there are not disk/memory issues on the server. That proves nothing. It might be a PostgreSQL bug but just as well it might be a silent disk corruption somewhere, unspotted for a long time. > If I try to execute a vacuum full I get this error. > ERROR: could not open relation 1663/36509/28638634: No such file or directory > > At this time I saw two entries of table "cliente" in the pg_tables. > > At this time I stopped to troubleshoot and tried to plan some tasks to > recover the disaster situation. > > I recovered the 98% of the data by copying manually the physical data file of > the cluster of that table and the clog to another pg server 8.3. > after that in the server where i got that problem I did this: Not sure what you mean by 'physical data file of the cluster' but you should do a file-level backup of the whole cluster right now. Before trying to fix the issues (possibly damaging the data). Then get the last 8.3.x release (if you're using an old one). > - renamed the table to cliente_prova > - removed all the foreign key that are pointing to that table > - recreated the table > - populated the table with the production data recovered from the other > server (the last 3-4 fields of the table was unreadable, but I don't know if > the method that I used to "recover" the table was technically correct... It > was a try...) > - all up and running again What do you mean by 'populated the table' with the production data? How did you do that? kind regards Tomas -- 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] Complex transactions without using plPgSQL Functions. It is possible?
On Wed, Mar 7, 2012 at 6:30 AM, Andre Lopes wrote: > I'm writing a web application that uses PostgreSQL and I need to do > some operations where I read/write to 3 tables in the same > transaction. Is what you're looking for simply the "begin transaction"[1] and "commit"[2] commands? With those, you can write your code to do whatever it likes, and it'll still be all one transaction. With some caveats, of course, but if all you're doing is INSERT / DELETE / UPDATE, you'll be fully protected by the transaction boundaries (eg if your script dies unexpectedly in the middle, the whole thing will be rolled back, all those usual safeties). As a side point: Is it possible to disable Postgres's default autocommit behavior and have it automatically open a transaction on connection and after commit/rollback? That's what I grew up on with DB2 - you do some work, then you commit, then you do more work, then you commit, but never have to say "and begin a transaction too". Chris Angelico [1] http://www.postgresql.org/docs/current/static/sql-begin.html [2] http://www.postgresql.org/docs/current/static/sql-commit.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] corrupted table postgresql 8.3
Hi people! I have a pg 8.3. Today I issued in a database that comand: =# ALTER TABLE cliente ADD COLUMN pwd_expired boolean DEFAULT FALSE; WARNING: unexpected attrdef record found for attr 22 of rel cliente WARNING: unexpected attrdef record found for attr 22 of rel cliente WARNING: unexpected attrdef record found for attr 22 of rel cliente ALTER TABLE Time: 1184.404 ms After that the table was empty. SELECT * from cliente; 0 rows ;) Should contain about 90k records. I checked in the logs and there are not disk/memory issues on the server. If I try to execute a vacuum full I get this error. ERROR: could not open relation 1663/36509/28638634: No such file or directory At this time I saw two entries of table "cliente" in the pg_tables. At this time I stopped to troubleshoot and tried to plan some tasks to recover the disaster situation. I recovered the 98% of the data by copying manually the physical data file of the cluster of that table and the clog to another pg server 8.3. after that in the server where i got that problem I did this: - renamed the table to cliente_prova - removed all the foreign key that are pointing to that table - recreated the table - populated the table with the production data recovered from the other server (the last 3-4 fields of the table was unreadable, but I don't know if the method that I used to "recover" the table was technically correct... It was a try...) - all up and running again Now i'm facing this: - if I type \d cliente, I see the schema twice, one without the field "pwd_expired" and the right one that is without it (the table that I restored) - unable to drop the table renamed cliente_prova =# DROP TABLE cliente_prova; WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova NOTICE: default for table cliente column id depends on sequence cliente_id_seq WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova WARNING: unexpected attrdef record found for attr 22 of rel cliente_prova ERROR: cannot drop table cliente_prova because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too. I'll stop here to describe further detail... can you suppose what is happened please? After that can we discuss how to cleanup the situation...;) Thanks a lot! Matteo -- 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] Single server multiple databases - extension
Any one know how to install extensions to multiple databases in the same server ? Thanks in advance Brian From: Brian Trudal To: "pgsql-general@postgresql.org" Sent: Monday, March 5, 2012 4:52 PM Subject: Single server multiple databases - extension Hi I have 2 databases running in a single server; and I installed extension 'hstore' to one database and it works fine. When I tried to use the same extension in another database, it gives an error saying 'extension does not exist'; nor it allow to install as it complains about its existence. Any help ? db1=# CREATE EXTENSION hstore; ERROR: type "hstore" already exists db1=# DROP EXTENSION hstore; ERROR: extension "hstore" does not exist db1=# create table foo(id hstore); ERROR: type "hstore" is only a shell LINE 1: create table foo(id hstore); ^
Re: [GENERAL] Unhelpful initdb error message
Tom Lane wrote: > > Fascinating. So maybe there is something to Bosco's theory of something > holding open the old pidfile. There could also have been a corrupt in-memory/cached descriptor in the filesystem code that never needed flushing to disk? That would help explain why it fully went away after the reboot and yet the on-disk stuff seems fine. > But what would that be? Possibly a 3rd party/home-grown monitoring program? Bosco. -- 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] Unhelpful initdb error message
On 6 March 2012 19:28, Tom Lane wrote: > Thom Brown writes: >> On 6 March 2012 18:20, Tom Lane wrote: >>> Still, I agree with your point: Thom should reboot and see if the >>> misbehavior is still there, because that would be useful info for his >>> bug report. > >> After a reboot, initdb completes successfully. I don't think it >> performed an fsck of any kind as I don't see it in the logs. > > Fascinating. So maybe there is something to Bosco's theory of something > holding open the old pidfile. But what would that be? The postmaster > doesn't hold it open, just write it and close it. No idea. I did run an lsof while the problem was still present and grep'd for the directory as I too suspected there may be some process thinking it still had a reference to the file, but there were no matches. -- Thom -- 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] Complex transactions without using plPgSQL Functions. It is possible?
On 03/06/2012 11:30 AM, Andre Lopes wrote: Hi, I'm writing a web application that uses PostgreSQL and I need to do some operations where I read/write to 3 tables in the same transaction. To do this I need to store the values of variables and I'm not sure if it is possible to do this without using plPgSQL. [code] SELECT count(email) INTO vCONTA_HIST FROM am_newsletter_hist_alter WHERE email = pEMAIL AND id_website_recolha = pID_WEBSITE_RECOLHA; IF vCONTA_HIST = 0 THEN vNUM_ALTER := 1; ELSE SELECT MAX(num_alter) INTO vNUM_ALTER_ACT FROM am_newsletter_hist_alter WHERE email = pEMAIL AND id_website_recolha = pID_WEBSITE_RECOLHA LIMIT 1; vNUM_ALTER := vNUM_ALTER_ACT + 1; END IF; [/code] This is the plPgSQL code that I need to write in Python. It is possible to do this without using PlPgSQL? Sure: http://www.postgresql.org/docs/9.0/interactive/plpython-database.html Best Regards, -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Complex transactions without using plPgSQL Functions. It is possible?
Hi, I'm writing a web application that uses PostgreSQL and I need to do some operations where I read/write to 3 tables in the same transaction. To do this I need to store the values of variables and I'm not sure if it is possible to do this without using plPgSQL. [code] SELECT count(email) INTO vCONTA_HIST FROM am_newsletter_hist_alter WHERE email = pEMAIL AND id_website_recolha = pID_WEBSITE_RECOLHA; IF vCONTA_HIST = 0 THEN vNUM_ALTER := 1; ELSE SELECT MAX(num_alter) INTO vNUM_ALTER_ACT FROM am_newsletter_hist_alter WHERE email = pEMAIL AND id_website_recolha = pID_WEBSITE_RECOLHA LIMIT 1; vNUM_ALTER := vNUM_ALTER_ACT + 1; END IF; [/code] This is the plPgSQL code that I need to write in Python. It is possible to do this without using PlPgSQL? Best Regards, -- 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] Unhelpful initdb error message
Thom Brown writes: > On 6 March 2012 18:20, Tom Lane wrote: >> Still, I agree with your point: Thom should reboot and see if the >> misbehavior is still there, because that would be useful info for his >> bug report. > After a reboot, initdb completes successfully. I don't think it > performed an fsck of any kind as I don't see it in the logs. Fascinating. So maybe there is something to Bosco's theory of something holding open the old pidfile. But what would that be? The postmaster doesn't hold it open, just write it and close it. regards, tom lane -- 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] Unhelpful initdb error message
On 6 March 2012 18:51, dennis jenkins wrote: > On Tue, Mar 6, 2012 at 10:11 AM, Thom Brown wrote: >> On 6 March 2012 16:04, Adrian Klaver wrote: >>> The postmaster.pid is located outside the data directory, but points back >>> to the >>> data directory. Not sure where Debian, though at a guess somewhere in >>> /var. >>> Any way search for postmaster.pid. >> >> I'm not sure, because if I use a new data directory, initdb it and >> start the service, the postmaster.pid appears in it, and not as a >> symbolic link. >> >> I did a search for postmaster.pid in the whole of /var and it only >> shows up "/var/lib/postgresql/9.1/main/postmaster.pid" >> >> -- >> Thom > > I know that I'm late to the party, but a small suggestion: Run > "initdb" with "strace" (truss on Solaris) and examine the syscalls > made. It should show you, conclusively, what files are being > "open"ed, "unlink"ed, etc... > > Example: > > strace -o /tmp/x initdb -D /tmp/data-1 > grep -E '^(open|unlink)' /tmp/x The reboot removed the opportunity to do this unfortunately. I'll have to wait an see if it happens again, but if it does, I'll try the suggestion. -- Thom -- 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] Unhelpful initdb error message
On 6 March 2012 18:20, Tom Lane wrote: > Bosco Rama writes: >> Thom Brown wrote: >>> I've done that a couple times, but no effect. I think Tom's point >>> about a filesystem bug is probably right. > >> Have you rebooted since this started? There may be a process that is >> holding the pid file 'deleted but present' until the process terminates. > > Even if something is holding the file open, that wouldn't prevent unlink > from removing the directory entry for it; or even if we were talking > about a badly-designed filesystem that failed to follow standard Unix > semantics, that wouldn't explain why the directory entry is apparently > visible to some operations but not others. > > Still, I agree with your point: Thom should reboot and see if the > misbehavior is still there, because that would be useful info for his > bug report. After a reboot, initdb completes successfully. I don't think it performed an fsck of any kind as I don't see it in the logs. -- Thom -- 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] Unhelpful initdb error message
On Tue, Mar 6, 2012 at 10:11 AM, Thom Brown wrote: > On 6 March 2012 16:04, Adrian Klaver wrote: >> The postmaster.pid is located outside the data directory, but points back to >> the >> data directory. Not sure where Debian, though at a guess somewhere in /var. >> Any way search for postmaster.pid. > > I'm not sure, because if I use a new data directory, initdb it and > start the service, the postmaster.pid appears in it, and not as a > symbolic link. > > I did a search for postmaster.pid in the whole of /var and it only > shows up "/var/lib/postgresql/9.1/main/postmaster.pid" > > -- > Thom I know that I'm late to the party, but a small suggestion: Run "initdb" with "strace" (truss on Solaris) and examine the syscalls made. It should show you, conclusively, what files are being "open"ed, "unlink"ed, etc... Example: strace -o /tmp/x initdb -D /tmp/data-1 grep -E '^(open|unlink)' /tmp/x -- 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] Adding a lot of tables
On 06/03/2012 16:58, jan.mus...@giub.unibe.ch wrote: > Dear All, > > When I am adding (just commands CREATE TABLE and CREATE TRIGER) a > bunch of tables (3000) to my db first everything goes fast but after > some minutes the new tables are added at the speed of a snail. Does > anybody know what could be the reason? Slow disk access? - only a guess, mind. You'll need to provide LOTS more information before people can help. Platform, hardware, PG version, etc etc etc. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Unhelpful initdb error message
Bosco Rama writes: > Thom Brown wrote: >> I've done that a couple times, but no effect. I think Tom's point >> about a filesystem bug is probably right. > Have you rebooted since this started? There may be a process that is > holding the pid file 'deleted but present' until the process terminates. Even if something is holding the file open, that wouldn't prevent unlink from removing the directory entry for it; or even if we were talking about a badly-designed filesystem that failed to follow standard Unix semantics, that wouldn't explain why the directory entry is apparently visible to some operations but not others. Still, I agree with your point: Thom should reboot and see if the misbehavior is still there, because that would be useful info for his bug report. regards, tom lane -- 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] Unhelpful initdb error message
Sry, forgot to add list. Thom Brown wrote: > > I've done that a couple times, but no effect. I think Tom's point > about a filesystem bug is probably right. Have you rebooted since this started? There may be a process that is holding the pid file 'deleted but present' until the process terminates. If you can't find the process to kill it a reboot would remove all doubt. Just a thought. Bosco. -- 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] Unhelpful initdb error message
Thom Brown writes: > On 6 March 2012 18:01, Adrian Klaver wrote: >> A thought, what if you do rm -rf * in the data directory? > I've done that a couple times, but no effect. I think Tom's point > about a filesystem bug is probably right. Yeah, given your "touch" experiment I think that you have more than enough ammunition to file a kernel bug. Apparently, the directory contents are corrupted in such a way that a file named "postmaster.pid" can be created but it's invisible to some (perhaps not all) operations. In some of the more complex directory data structures I could believe that this result is filename-sensitive (think corrupted hashtable...) regards, tom lane -- 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] Unhelpful initdb error message
On Tue, Mar 6, 2012 at 19:03, Thom Brown wrote: > On 6 March 2012 18:01, Adrian Klaver wrote: >> On Tuesday, March 06, 2012 9:53:52 am Tom Lane wrote: >>> Thom Brown writes: >>> > /home/thom/Development/data was causing problems so: >>> > >>> > mv data databroken >>> > mkdir data >>> > initdb >>> > >>> > ... working fine again. I then used the postmaster.pid from this when >>> > started up. But if I do: >>> > >>> > pg_ctl stop >>> > rm -rf data >>> > mv databroken data >>> > initdb >>> > >>> > ... error messages appear again. >>> >>> Okay, so the question becomes: what is different between databroken and >>> a freshly mkdir'd empty directory? If there is no visible difference in >>> contents, ownership, or permissions, then it seems like this is evidence >>> of a filesystem bug (ie, apparently empty directory acts nonempty for >>> some operations). >> >> A thought, what if you do rm -rf * in the data directory? > > I've done that a couple times, but no effect. I think Tom's point > about a filesystem bug is probably right. You mentioned encryptfs, right? That's where I'd be looking first :-O it wasn't obvious enough to throw something in your kernel dmesg log by any chance? :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- 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] Unhelpful initdb error message
On 6 March 2012 18:01, Adrian Klaver wrote: > On Tuesday, March 06, 2012 9:53:52 am Tom Lane wrote: >> Thom Brown writes: >> > /home/thom/Development/data was causing problems so: >> > >> > mv data databroken >> > mkdir data >> > initdb >> > >> > ... working fine again. I then used the postmaster.pid from this when >> > started up. But if I do: >> > >> > pg_ctl stop >> > rm -rf data >> > mv databroken data >> > initdb >> > >> > ... error messages appear again. >> >> Okay, so the question becomes: what is different between databroken and >> a freshly mkdir'd empty directory? If there is no visible difference in >> contents, ownership, or permissions, then it seems like this is evidence >> of a filesystem bug (ie, apparently empty directory acts nonempty for >> some operations). > > A thought, what if you do rm -rf * in the data directory? I've done that a couple times, but no effect. I think Tom's point about a filesystem bug is probably right. -- Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump : no tables were found.
Hi, I m trying to take backup of data of a particular table using pg_dump. I used double quotes for table name but output is : pg_dump : no tables were found. Command used : -h localhost -p 5432 -U postgres -W -F p -a -t '"TestTable"' -f DbBackup/BackupTableActions.sql TestDataBase This problem only shows when there is a upper case character in my table name. Please Help Thanks & Regards Piyush
Re: [GENERAL] what Linux to run
Hmm... I also use 64 bit Fedora 16, on an AMD quad core at home, and on a dual Xeon quad cores at work. For a desktop environment, I would recommend xfce for serious work over GNOME 3. However, GNOME 3 is fine if you prefer fashion over functionality. I have 25 virtual desktops, and make full use of not only multiple tabs on Firefox, but also on the nautilus directory and GNOME terminal windows -- I also have useful applets on panels that auto hide, etc., GNOME 2 could support that, but not GNOME 3! What are the problems of Java 7 on FC16? I am curious, as I am building a system using Java 7 on FC16 using JBoss 7.1 backed by PostgreSQL 9.1. If anyone is interested, I have a bash script that installs JBoss 7.1 and converts it to use PostgreSQL. Cheers, Gavin On 06/03/12 01:25, r d wrote: >> >> If we move to Linux, what is the preferred Linux for running Postgres >> on. This machine would be dedicated to the database only.=20 >> >> I'd like a recommendation for both a GUI hosted version and a non-GUI >> version. I haven't used Linux in the past but did spend several year s >> in a mixed Unix and IBM mainframe environment at the console level. I run PostgreSQL on Fedora Core 16 64bit and have never had problems, now or before. From that point of view I can recommend FC, but I don't know how it compares performance-wise to other distros. I have been using the FC series since they split from the "RedHat Linux" distribs at about "RedHat 9", perhaps 10 years ago and have never missed anything, and seldom noticed troublesome behavior. My main criticism of FC is that the distro updates to a new version quite often, 1-2 times per year, and upgrades are seldom as smooth as they are supposed/advertised to be, but they have become much better. Beyond that, the FC series have about everything you need for development or anything else, like running PG You can use FC both with GUI and without. It comes by default with GNOME. It also has KDE, which looks (and works) similar to Windows. Both Gnome and KDE run atop X. FC has the usual Unix shells like bash (default), sh, ksh, csh, tcsh ... and if you need to connect to your host, there are several 3270 emulator available, for X and also text-mode. Two components which do not mix well with FC are Java 7 (1.7.0x) and Oracle RDBMS 11g. For Java, stay with the 1.6 series until the problems of 1.7 are fixed. If you need to use the RDBMS besides PG then FC is not your OS. Instead, look at what systems they (Oracle) "support". I hope this helps you with your decision.
Re: [GENERAL] Unhelpful initdb error message
On Tuesday, March 06, 2012 9:53:52 am Tom Lane wrote: > Thom Brown writes: > > /home/thom/Development/data was causing problems so: > > > > mv data databroken > > mkdir data > > initdb > > > > ... working fine again. I then used the postmaster.pid from this when > > started up. But if I do: > > > > pg_ctl stop > > rm -rf data > > mv databroken data > > initdb > > > > ... error messages appear again. > > Okay, so the question becomes: what is different between databroken and > a freshly mkdir'd empty directory? If there is no visible difference in > contents, ownership, or permissions, then it seems like this is evidence > of a filesystem bug (ie, apparently empty directory acts nonempty for > some operations). A thought, what if you do rm -rf * in the data directory? > > regards, tom lane -- Adrian Klaver adrian.kla...@gmail.com -- 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] Unhelpful initdb error message
On 6 March 2012 17:53, Tom Lane wrote: > Thom Brown writes: >> /home/thom/Development/data was causing problems so: > >> mv data databroken >> mkdir data >> initdb > >> ... working fine again. I then used the postmaster.pid from this when >> started up. But if I do: > >> pg_ctl stop >> rm -rf data >> mv databroken data >> initdb > >> ... error messages appear again. > > Okay, so the question becomes: what is different between databroken and > a freshly mkdir'd empty directory? If there is no visible difference in > contents, ownership, or permissions, then it seems like this is evidence > of a filesystem bug (ie, apparently empty directory acts nonempty for > some operations). You may well be right. There appear to be dark forces at work here: thom@swift:~/Development/data$ touch postmaster.pid thom@swift:~/Development/data$ ls -l total 0 thom@swift:~/Development/data$ touch file.txt thom@swift:~/Development/data$ ls -l total 8 -rw-rw-r-- 1 thom thom 0 2012-03-06 17:59 file.txt -- Thom -- 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] Unhelpful initdb error message
On Tuesday, March 06, 2012 9:48:51 am Thom Brown wrote: > On 6 March 2012 17:45, Adrian Klaver wrote: > > On Tuesday, March 06, 2012 9:25:17 am Thom Brown wrote: > >> These are in my env output: > >> > >> PATH=/home/thom/Development/psql/bin/:/usr/lib/lightdm/lightdm:/usr/loca > >> l/s bin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games > >> PGDATA=/home/thom/Development/data/ > >> PGPORT=5488 > >> > >> This appears in my build script before configure: > >> > >> export PGDATA=$HOME/Development/data/ > >> export PATH=$HOME/Development/psql/bin/:$PATH > >> export PGPORT=5488 > >> > >> And those 3 lines also appear in my .bashrc file without any variation: > >> > >> export PGDATA=$HOME/Development/data/ > >> export PATH=$HOME/Development/psql/bin/:$PATH > >> export PGPORT=5488 > > > > And you are sure there is no pg_ctl or initdb outside > > /usr/lib/postgresql/9.1/bin or /home/thom/Development/psql/bin and in > > your PATH? So that would be no:)? > > > > Just for grins what happens if you try an initdb using an explicit > > reference to the binary /home/thom/Development/psql/bin/initdb and the > > -D > > /home/thom/Development/data/ ? > > thom@swift:~/Development$ /home/thom/Development/psql/bin/initdb -E > 'UTF8' -D /home/thom/Development/data/ > The files belonging to this database system will be owned by user "thom". > This user must also own the server process. > > The database cluster will be initialized with locale en_GB.UTF-8. > The default text search configuration will be set to "english". > > fixing permissions on existing directory /home/thom/Development/data ... ok > creating subdirectories ... ok > selecting default max_connections ... 10 > selecting default shared_buffers ... 400kB > creating configuration files ... ok > creating template1 database in /home/thom/Development/data/base/1 ... > FATAL: could not remove old lock file "postmaster.pid": No such file > or directory > HINT: The file seems accidentally left over, but it could not be > removed. Please remove the file by hand and try again. > child process exited with exit code 1 > initdb: removing contents of data directory "/home/thom/Development/data" Its official, I'm stumped. Information seems to be persisting between sessions and absent some other cluster then the ones you have indicated I don't where that information is coming from? -- Adrian Klaver adrian.kla...@gmail.com -- 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] Unhelpful initdb error message
Thom Brown writes: > /home/thom/Development/data was causing problems so: > mv data databroken > mkdir data > initdb > ... working fine again. I then used the postmaster.pid from this when > started up. But if I do: > pg_ctl stop > rm -rf data > mv databroken data > initdb > ... error messages appear again. Okay, so the question becomes: what is different between databroken and a freshly mkdir'd empty directory? If there is no visible difference in contents, ownership, or permissions, then it seems like this is evidence of a filesystem bug (ie, apparently empty directory acts nonempty for some operations). regards, tom lane -- 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] Unhelpful initdb error message
On 6 March 2012 17:46, Tom Lane wrote: > Thom Brown writes: >> On 6 March 2012 16:31, Tom Lane wrote: >>> [ scratches head... ] I can't reproduce it with current git tip. > >> And I don't think I can reproduce this if I remove that directory. >> I've seen this issue about 3 or 4 times in the past, and fixed it by >> ditching the old data dir completely. I'm just not sure what causes >> this to happen. > > I'm a bit confused here. Isn't the data directory totally empty before > initdb starts? It's supposed to refuse to proceed otherwise. Yes, it is completely empty: thom@swift:~/Development$ ls -la data total 8 drwx-- 2 thom thom 4096 2012-03-06 17:48 . drwxrwxr-x 15 thom thom 4096 2012-03-06 17:46 .. -- Thom -- 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] Unhelpful initdb error message
On 6 March 2012 17:45, Adrian Klaver wrote: > On Tuesday, March 06, 2012 9:25:17 am Thom Brown wrote: > >> >> These are in my env output: >> >> PATH=/home/thom/Development/psql/bin/:/usr/lib/lightdm/lightdm:/usr/local/s >> bin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games >> PGDATA=/home/thom/Development/data/ >> PGPORT=5488 >> >> This appears in my build script before configure: >> >> export PGDATA=$HOME/Development/data/ >> export PATH=$HOME/Development/psql/bin/:$PATH >> export PGPORT=5488 >> >> And those 3 lines also appear in my .bashrc file without any variation: >> >> export PGDATA=$HOME/Development/data/ >> export PATH=$HOME/Development/psql/bin/:$PATH >> export PGPORT=5488 > > And you are sure there is no pg_ctl or initdb outside > /usr/lib/postgresql/9.1/bin or /home/thom/Development/psql/bin and in your > PATH? > > Just for grins what happens if you try an initdb using an explicit reference > to > the binary /home/thom/Development/psql/bin/initdb and the -D > /home/thom/Development/data/ ? thom@swift:~/Development$ /home/thom/Development/psql/bin/initdb -E 'UTF8' -D /home/thom/Development/data/ The files belonging to this database system will be owned by user "thom". This user must also own the server process. The database cluster will be initialized with locale en_GB.UTF-8. The default text search configuration will be set to "english". fixing permissions on existing directory /home/thom/Development/data ... ok creating subdirectories ... ok selecting default max_connections ... 10 selecting default shared_buffers ... 400kB creating configuration files ... ok creating template1 database in /home/thom/Development/data/base/1 ... FATAL: could not remove old lock file "postmaster.pid": No such file or directory HINT: The file seems accidentally left over, but it could not be removed. Please remove the file by hand and try again. child process exited with exit code 1 initdb: removing contents of data directory "/home/thom/Development/data" -- Thom -- 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] Unhelpful initdb error message
On Tuesday, March 06, 2012 9:43:00 am Tom Lane wrote: > Adrian Klaver writes: > > The postmaster.pid is located outside the data directory, but points back > > to the data directory. Not sure where Debian, though at a guess > > somewhere in /var. Any way search for postmaster.pid. > > Really? That seems like an extremely dangerous/stupid/unnecessary hack > on the part of the Debian packagers. What's keeping users from > accidentally starting two postmasters in the same data directory, if > they can put their pidfiles in (different) other places? No, that was a mistake on my part. It is in the $DATA directory. > > (This seems unrelated to Thom's issue, but it's still worrisome.) > > regards, tom lane -- Adrian Klaver adrian.kla...@gmail.com -- 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] Unhelpful initdb error message
Thom Brown writes: > On 6 March 2012 16:31, Tom Lane wrote: >> [ scratches head... ] I can't reproduce it with current git tip. > And I don't think I can reproduce this if I remove that directory. > I've seen this issue about 3 or 4 times in the past, and fixed it by > ditching the old data dir completely. I'm just not sure what causes > this to happen. I'm a bit confused here. Isn't the data directory totally empty before initdb starts? It's supposed to refuse to proceed otherwise. regards, tom lane -- 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] Unhelpful initdb error message
On Tuesday, March 06, 2012 9:25:17 am Thom Brown wrote: > > These are in my env output: > > PATH=/home/thom/Development/psql/bin/:/usr/lib/lightdm/lightdm:/usr/local/s > bin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games > PGDATA=/home/thom/Development/data/ > PGPORT=5488 > > This appears in my build script before configure: > > export PGDATA=$HOME/Development/data/ > export PATH=$HOME/Development/psql/bin/:$PATH > export PGPORT=5488 > > And those 3 lines also appear in my .bashrc file without any variation: > > export PGDATA=$HOME/Development/data/ > export PATH=$HOME/Development/psql/bin/:$PATH > export PGPORT=5488 And you are sure there is no pg_ctl or initdb outside /usr/lib/postgresql/9.1/bin or /home/thom/Development/psql/bin and in your PATH? Just for grins what happens if you try an initdb using an explicit reference to the binary /home/thom/Development/psql/bin/initdb and the -D /home/thom/Development/data/ ? -- Adrian Klaver adrian.kla...@gmail.com -- 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] Unhelpful initdb error message
Adrian Klaver writes: > The postmaster.pid is located outside the data directory, but points back to > the > data directory. Not sure where Debian, though at a guess somewhere in /var. > Any way search for postmaster.pid. Really? That seems like an extremely dangerous/stupid/unnecessary hack on the part of the Debian packagers. What's keeping users from accidentally starting two postmasters in the same data directory, if they can put their pidfiles in (different) other places? (This seems unrelated to Thom's issue, but it's still worrisome.) regards, tom lane -- 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] Unhelpful initdb error message
On 6 March 2012 17:16, Tom Lane wrote: > Thom Brown writes: >> Looking back through my terminal log, one thing might lend a clue from >> before I tried rebuliding it: > >> thom@swift:~/Development$ pg_ctl stop >> waiting for server to shut downcd .postgre.s >> . >> > > > >> ^C >> thom@swift:~/Development$ pg_ctl stop >> pg_ctl: could not send stop signal (PID: 2807): No such process >> thom@swift:~/Development$ ps -ef | grep postgres >> postgres 1199 1 0 Mar04 ? 00:00:01 >> /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main >> -c config_file=/etc/postgresql/9.1/main/postgresql.conf >> postgres 1273 1199 0 Mar04 ? 00:00:18 postgres: writer >> process >> postgres 1274 1199 0 Mar04 ? 00:00:14 postgres: wal writer >> process >> postgres 1275 1199 0 Mar04 ? 00:00:03 postgres: autovacuum >> launcher process >> postgres 1276 1199 0 Mar04 ? 00:00:02 postgres: stats >> collector process >> thom 16476 4302 0 15:30 pts/1 00:00:00 grep --color=auto postgres > > Hm. It looks like pg_ctl found a PID file pointing to a non-existent > process, which is a bit like what you're seeing initdb do. > > I wonder whether this is somehow caused by conflicting settings for > PGDATA. Do you have a setting for that in your environment, or .bashrc > or someplace, that is different from what you're trying to use? These are in my env output: PATH=/home/thom/Development/psql/bin/:/usr/lib/lightdm/lightdm:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games PGDATA=/home/thom/Development/data/ PGPORT=5488 This appears in my build script before configure: export PGDATA=$HOME/Development/data/ export PATH=$HOME/Development/psql/bin/:$PATH export PGPORT=5488 And those 3 lines also appear in my .bashrc file without any variation: export PGDATA=$HOME/Development/data/ export PATH=$HOME/Development/psql/bin/:$PATH export PGPORT=5488 -- Thom -- 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] Unhelpful initdb error message
On Tuesday, March 06, 2012 9:09:41 am Thom Brown wrote: > On 6 March 2012 17:00, Adrian Klaver wrote: > > On Tuesday, March 06, 2012 8:44:10 am Thom Brown wrote: > >> >> And if I start my development copy, this is the content of its > >> >> postmaster.pid: > >> >> > >> >> 27061 > >> >> /home/thom/Development/data > >> >> 1331050950 > >> >> 5488 > >> >> /tmp > >> >> localhost > >> >> 5488001 191365126 > >> > > >> > So how are getting the file above? I thought initdb refused to init > >> > the directory and that you could not find pid file it was referring > >> > to? Just on a hunch, what is in /tmp? > >> > >> I got the above output when I created a new data directory and initdb'd > >> it. > > > > Still not understanding. In your original post you said > > /home/thom/Development/data was the original directory you could not > > initdb. How could it also be the new directory you can initdb as > > indicated by the postmaster.pid? > > /home/thom/Development/data was causing problems so: > > mv data databroken > mkdir data > initdb > > ... working fine again. I then used the postmaster.pid from this when > started up. But if I do: > > pg_ctl stop > rm -rf data > mv databroken data > initdb > > ... error messages appear again. Humph, need more coffee. > > > From your previous post: > > thom@swift:~/Development$ pg_ctl stop > > pg_ctl: could not send stop signal (PID: 2807): No such process > > > > Doing the above without qualifying which version of pg_ctl you are using > > or what data directory you are pointing is dangerous. The combination > > of implied pathing and preset env variables could lead to all sorts of > > mischief. > > Unlikely since pg_ctl isn't available in my search path once I remove > my local development bin dir from it. All non-client tools for the > packaged version aren't available to normal users. Those are all in > /usr/lib/postgresql/9.1/bin. The only ones exposed to the search path > through symbolic links are: env variables? -- Adrian Klaver adrian.kla...@gmail.com -- 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] Unhelpful initdb error message
Thom Brown writes: > Looking back through my terminal log, one thing might lend a clue from > before I tried rebuliding it: > thom@swift:~/Development$ pg_ctl stop > waiting for server to shut downcd .postgre.s > . > > ^C > thom@swift:~/Development$ pg_ctl stop > pg_ctl: could not send stop signal (PID: 2807): No such process > thom@swift:~/Development$ ps -ef | grep postgres > postgres 1199 1 0 Mar04 ?00:00:01 > /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main > -c config_file=/etc/postgresql/9.1/main/postgresql.conf > postgres 1273 1199 0 Mar04 ?00:00:18 postgres: writer > process > postgres 1274 1199 0 Mar04 ?00:00:14 postgres: wal writer > process > postgres 1275 1199 0 Mar04 ?00:00:03 postgres: autovacuum > launcher process > postgres 1276 1199 0 Mar04 ?00:00:02 postgres: stats > collector process > thom 16476 4302 0 15:30 pts/100:00:00 grep --color=auto postgres Hm. It looks like pg_ctl found a PID file pointing to a non-existent process, which is a bit like what you're seeing initdb do. I wonder whether this is somehow caused by conflicting settings for PGDATA. Do you have a setting for that in your environment, or .bashrc or someplace, that is different from what you're trying to use? regards, tom lane -- 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] Unhelpful initdb error message
On 6 March 2012 17:00, Adrian Klaver wrote: > On Tuesday, March 06, 2012 8:44:10 am Thom Brown wrote: > >> >> And if I start my development copy, this is the content of its >> >> postmaster.pid: >> >> >> >> 27061 >> >> /home/thom/Development/data >> >> 1331050950 >> >> 5488 >> >> /tmp >> >> localhost >> >> 5488001 191365126 >> > >> > So how are getting the file above? I thought initdb refused to init the >> > directory and that you could not find pid file it was referring to? Just >> > on a hunch, what is in /tmp? >> >> I got the above output when I created a new data directory and initdb'd it. > > Still not understanding. In your original post you said > /home/thom/Development/data was the original directory you could not initdb. > How > could it also be the new directory you can initdb as indicated by the > postmaster.pid? /home/thom/Development/data was causing problems so: mv data databroken mkdir data initdb ... working fine again. I then used the postmaster.pid from this when started up. But if I do: pg_ctl stop rm -rf data mv databroken data initdb ... error messages appear again. > From your previous post: > thom@swift:~/Development$ pg_ctl stop > pg_ctl: could not send stop signal (PID: 2807): No such process > > Doing the above without qualifying which version of pg_ctl you are using or > what > data directory you are pointing is dangerous. The combination of implied > pathing and preset env variables could lead to all sorts of mischief. Unlikely since pg_ctl isn't available in my search path once I remove my local development bin dir from it. All non-client tools for the packaged version aren't available to normal users. Those are all in /usr/lib/postgresql/9.1/bin. The only ones exposed to the search path through symbolic links are: clusterdb createdb createlang createuser dropdb droplang dropuser pg_dump pg_dumpall pg_restore psql reindexdb vacuumdb vacuumlo -- Thom -- 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] Unhelpful initdb error message
On Tuesday, March 06, 2012 8:44:10 am Thom Brown wrote: > >> And if I start my development copy, this is the content of its > >> postmaster.pid: > >> > >> 27061 > >> /home/thom/Development/data > >> 1331050950 > >> 5488 > >> /tmp > >> localhost > >> 5488001 191365126 > > > > So how are getting the file above? I thought initdb refused to init the > > directory and that you could not find pid file it was referring to? Just > > on a hunch, what is in /tmp? > > I got the above output when I created a new data directory and initdb'd it. Still not understanding. In your original post you said /home/thom/Development/data was the original directory you could not initdb. How could it also be the new directory you can initdb as indicated by the postmaster.pid? From your previous post: thom@swift:~/Development$ pg_ctl stop pg_ctl: could not send stop signal (PID: 2807): No such process Doing the above without qualifying which version of pg_ctl you are using or what data directory you are pointing is dangerous. The combination of implied pathing and preset env variables could lead to all sorts of mischief. > > /tmp shows: > > 4 -rw--- 1 thomthom 55 2012-03-06 16:22 > .s.PGSQL.5488.lock > 0 srwxrwxrwx 1 thomthom0 2012-03-06 16:22 > .s.PGSQL.5488 > > Once it's up and running. These disappear after though. When using > the old data directory again, there's no evidence of anything like > this in /tmp. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Adding a lot of tables
Dear All, When I am adding (just commands CREATE TABLE and CREATE TRIGER) a bunch of tables (3000) to my db first everything goes fast but after some minutes the new tables are added at the speed of a snail. Does anybody know what could be the reason? All the best, Jan Musial -- 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] Unhelpful initdb error message
On 6 March 2012 16:40, Adrian Klaver wrote: > On Tuesday, March 06, 2012 8:24:20 am Thom Brown wrote: >> >> >> No, only the ones running as the postgres user. > > In my original read, I missed the part you had the Ubuntu/Debian packaged > version running. > >> >> Here's the contents of the pid file in /var/lib/postgresql/9.1/main/ >> >> 1199 >> /var/lib/postgresql/9.1/main >> 1330883367 >> 5432 >> /var/run/postgresql >> localhost >> 5432001 0 >> >> And if I start my development copy, this is the content of its >> postmaster.pid: >> >> 27061 >> /home/thom/Development/data >> 1331050950 >> 5488 >> /tmp >> localhost >> 5488001 191365126 > > So how are getting the file above? I thought initdb refused to init the > directory > and that you could not find pid file it was referring to? Just on a hunch, > what is > in /tmp? I got the above output when I created a new data directory and initdb'd it. /tmp shows: 4 -rw--- 1 thomthom 55 2012-03-06 16:22 .s.PGSQL.5488.lock 0 srwxrwxrwx 1 thomthom0 2012-03-06 16:22 .s.PGSQL.5488 Once it's up and running. These disappear after though. When using the old data directory again, there's no evidence of anything like this in /tmp. -- Thom -- 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] Unhelpful initdb error message
On Tuesday, March 06, 2012 8:24:20 am Thom Brown wrote: > > > No, only the ones running as the postgres user. In my original read, I missed the part you had the Ubuntu/Debian packaged version running. > > Here's the contents of the pid file in /var/lib/postgresql/9.1/main/ > > 1199 > /var/lib/postgresql/9.1/main > 1330883367 > 5432 > /var/run/postgresql > localhost > 5432001 0 > > And if I start my development copy, this is the content of its > postmaster.pid: > > 27061 > /home/thom/Development/data > 1331050950 > 5488 > /tmp > localhost > 5488001 191365126 So how are getting the file above? I thought initdb refused to init the directory and that you could not find pid file it was referring to? Just on a hunch, what is in /tmp? -- Adrian Klaver adrian.kla...@gmail.com -- 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] Unhelpful initdb error message
On 6 March 2012 16:31, Tom Lane wrote: > Thom Brown writes: >> On 6 March 2012 16:02, Tom Lane wrote: >>> Um ... I assume this is some patched version rather than pristine >>> sources? It's pretty hard to explain why it's falling over like that. > >> No, I did a "git stash", "git clean -f" and "git pull" before trying to >> build. > > [ scratches head... ] I can't reproduce it with current git tip. And I don't think I can reproduce this if I remove that directory. I've seen this issue about 3 or 4 times in the past, and fixed it by ditching the old data dir completely. I'm just not sure what causes this to happen. Looking back through my terminal log, one thing might lend a clue from before I tried rebuliding it: thom@swift:~/Development$ pg_ctl stop waiting for server to shut downcd .postgre.s . ^C thom@swift:~/Development$ pg_ctl stop pg_ctl: could not send stop signal (PID: 2807): No such process thom@swift:~/Development$ ps -ef | grep postgres postgres 1199 1 0 Mar04 ?00:00:01 /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf postgres 1273 1199 0 Mar04 ?00:00:18 postgres: writer process postgres 1274 1199 0 Mar04 ?00:00:14 postgres: wal writer process postgres 1275 1199 0 Mar04 ?00:00:03 postgres: autovacuum launcher process postgres 1276 1199 0 Mar04 ?00:00:02 postgres: stats collector process thom 16476 4302 0 15:30 pts/100:00:00 grep --color=auto postgres Postgres wouldn't shut down. I had no other terminal windows using psql, no other database client apps open, yet it stayed shutting down, so I CTRL+C'd it and tried again. A quick check of running processes showed that it had stopped running. (it shows postgres running above, but the dev copy runs as my user, not postgres) -- Thom -- 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] Unhelpful initdb error message
Thom Brown writes: > On 6 March 2012 16:02, Tom Lane wrote: >> Um ... I assume this is some patched version rather than pristine >> sources? It's pretty hard to explain why it's falling over like that. > No, I did a "git stash", "git clean -f" and "git pull" before trying to build. [ scratches head... ] I can't reproduce it with current git tip. regards, tom lane -- 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] Unhelpful initdb error message
On 6 March 2012 16:18, Adrian Klaver wrote: > On Tuesday, March 06, 2012 8:11:20 am Thom Brown wrote: >> On 6 March 2012 16:04, Adrian Klaver wrote: >> > The postmaster.pid is located outside the data directory, but points back >> > to the data directory. Not sure where Debian, though at a guess >> > somewhere in /var. Any way search for postmaster.pid. >> >> I'm not sure, because if I use a new data directory, initdb it and >> start the service, the postmaster.pid appears in it, and not as a >> symbolic link. >> >> I did a search for postmaster.pid in the whole of /var and it only >> shows up "/var/lib/postgresql/9.1/main/postmaster.pid" > > > My guess is if you open that file you will find it points back to the old > directory. So are you still running the Debian packaged version of Postgres? > Or in other words does a ps show any other postmasters running other than the > new one you built? No, only the ones running as the postgres user. Here's the contents of the pid file in /var/lib/postgresql/9.1/main/ 1199 /var/lib/postgresql/9.1/main 1330883367 5432 /var/run/postgresql localhost 5432001 0 And if I start my development copy, this is the content of its postmaster.pid: 27061 /home/thom/Development/data 1331050950 5488 /tmp localhost 5488001 191365126 -- Thom -- 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] Unhelpful initdb error message
On 6 March 2012 16:11, Thom Brown wrote: > On 6 March 2012 16:04, Adrian Klaver wrote: >> The postmaster.pid is located outside the data directory, but points back to >> the >> data directory. Not sure where Debian, though at a guess somewhere in /var. >> Any way search for postmaster.pid. > > I'm not sure, because if I use a new data directory, initdb it and > start the service, the postmaster.pid appears in it, and not as a > symbolic link. > > I did a search for postmaster.pid in the whole of /var and it only > shows up "/var/lib/postgresql/9.1/main/postmaster.pid" Correction, this is Ubuntu, not Debian. 11.10 if it's of any consequence. The file system is ext4 with rw,noatime,nodiratime,errors=remount-ro,commit=0 on a Crucial m4 SSD. ecryptfs is in use in the parent directory. -- Thom -- 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] Unhelpful initdb error message
On Tuesday, March 06, 2012 8:11:20 am Thom Brown wrote: > On 6 March 2012 16:04, Adrian Klaver wrote: > > The postmaster.pid is located outside the data directory, but points back > > to the data directory. Not sure where Debian, though at a guess > > somewhere in /var. Any way search for postmaster.pid. > > I'm not sure, because if I use a new data directory, initdb it and > start the service, the postmaster.pid appears in it, and not as a > symbolic link. > > I did a search for postmaster.pid in the whole of /var and it only > shows up "/var/lib/postgresql/9.1/main/postmaster.pid" My guess is if you open that file you will find it points back to the old directory. So are you still running the Debian packaged version of Postgres? Or in other words does a ps show any other postmasters running other than the new one you built? -- Adrian Klaver adrian.kla...@gmail.com -- 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] Unhelpful initdb error message
On 6 March 2012 16:04, Adrian Klaver wrote: > The postmaster.pid is located outside the data directory, but points back to > the > data directory. Not sure where Debian, though at a guess somewhere in /var. > Any way search for postmaster.pid. I'm not sure, because if I use a new data directory, initdb it and start the service, the postmaster.pid appears in it, and not as a symbolic link. I did a search for postmaster.pid in the whole of /var and it only shows up "/var/lib/postgresql/9.1/main/postmaster.pid" -- Thom -- 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] Unhelpful initdb error message
On 6 March 2012 16:02, Tom Lane wrote: > Thom Brown writes: >> thom@swift:~/Development$ initdb >> The files belonging to this database system will be owned by user "thom". >> This user must also own the server process. > >> The database cluster will be initialized with locale en_GB.UTF-8. >> The default database encoding has accordingly been set to UTF8. >> The default text search configuration will be set to "english". > >> fixing permissions on existing directory /home/thom/Development/data ... ok >> creating subdirectories ... ok >> selecting default max_connections ... 10 >> selecting default shared_buffers ... 400kB >> creating configuration files ... ok >> creating template1 database in /home/thom/Development/data/base/1 ... >> FATAL: could not remove old lock file "postmaster.pid": No such file >> or directory >> HINT: The file seems accidentally left over, but it could not be >> removed. Please remove the file by hand and try again. >> child process exited with exit code 1 >> initdb: removing contents of data directory "/home/thom/Development/data" > > Um ... I assume this is some patched version rather than pristine > sources? It's pretty hard to explain why it's falling over like that. No, I did a "git stash", "git clean -f" and "git pull" before trying to build. -- Thom -- 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] Unhelpful initdb error message
On Tuesday, March 06, 2012 7:46:37 am Thom Brown wrote: > Hi all, > > After building Postgres and trying an initdb, I'm getting the following: > > > thom@swift:~/Development$ initdb > The files belonging to this database system will be owned by user "thom". > This user must also own the server process. > > The database cluster will be initialized with locale en_GB.UTF-8. > The default database encoding has accordingly been set to UTF8. > The default text search configuration will be set to "english". > > fixing permissions on existing directory /home/thom/Development/data ... ok > creating subdirectories ... ok > selecting default max_connections ... 10 > selecting default shared_buffers ... 400kB > creating configuration files ... ok > creating template1 database in /home/thom/Development/data/base/1 ... > FATAL: could not remove old lock file "postmaster.pid": No such file > or directory > HINT: The file seems accidentally left over, but it could not be > removed. Please remove the file by hand and try again. > child process exited with exit code 1 > initdb: removing contents of data directory "/home/thom/Development/data" > > > It can't remove an old lock file due to it not existing, but the hint > says it was left over but couldn't be removed. The hint contradicts > the error message. There is nothing in the data directory at all > before trying this, and nothing after. Repeating initdb yields the > same result. > > But, if I rename the data directory to something else and mkdir data > again, all is well. I can make it break again by removing the new > data directory and renaming the old one back to data, still completely > empty. Note that throughout all of this, Postgres is running, but as > a separate user and using completely separate directories, since it's > the standard packaged version on Debian. > > Can anyone suggest what is wrong here? The postmaster.pid is located outside the data directory, but points back to the data directory. Not sure where Debian, though at a guess somewhere in /var. Any way search for postmaster.pid. -- Adrian Klaver adrian.kla...@gmail.com -- 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] Unhelpful initdb error message
Thom Brown writes: > thom@swift:~/Development$ initdb > The files belonging to this database system will be owned by user "thom". > This user must also own the server process. > The database cluster will be initialized with locale en_GB.UTF-8. > The default database encoding has accordingly been set to UTF8. > The default text search configuration will be set to "english". > fixing permissions on existing directory /home/thom/Development/data ... ok > creating subdirectories ... ok > selecting default max_connections ... 10 > selecting default shared_buffers ... 400kB > creating configuration files ... ok > creating template1 database in /home/thom/Development/data/base/1 ... > FATAL: could not remove old lock file "postmaster.pid": No such file > or directory > HINT: The file seems accidentally left over, but it could not be > removed. Please remove the file by hand and try again. > child process exited with exit code 1 > initdb: removing contents of data directory "/home/thom/Development/data" Um ... I assume this is some patched version rather than pristine sources? It's pretty hard to explain why it's falling over like that. I don't think there is anything wrong with the error message, because it's intended for the case where some previous postmaster failed and left a lock file behind. The question is how is it you're getting to that error, not whether we should change its text. One possible lead is that it looks like the postmaster-starting probes to select max_connections and shared_buffers all failed too, since those numbers came out as the minimums. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unhelpful initdb error message
Hi all, After building Postgres and trying an initdb, I'm getting the following: thom@swift:~/Development$ initdb The files belonging to this database system will be owned by user "thom". This user must also own the server process. The database cluster will be initialized with locale en_GB.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to "english". fixing permissions on existing directory /home/thom/Development/data ... ok creating subdirectories ... ok selecting default max_connections ... 10 selecting default shared_buffers ... 400kB creating configuration files ... ok creating template1 database in /home/thom/Development/data/base/1 ... FATAL: could not remove old lock file "postmaster.pid": No such file or directory HINT: The file seems accidentally left over, but it could not be removed. Please remove the file by hand and try again. child process exited with exit code 1 initdb: removing contents of data directory "/home/thom/Development/data" It can't remove an old lock file due to it not existing, but the hint says it was left over but couldn't be removed. The hint contradicts the error message. There is nothing in the data directory at all before trying this, and nothing after. Repeating initdb yields the same result. But, if I rename the data directory to something else and mkdir data again, all is well. I can make it break again by removing the new data directory and renaming the old one back to data, still completely empty. Note that throughout all of this, Postgres is running, but as a separate user and using completely separate directories, since it's the standard packaged version on Debian. Can anyone suggest what is wrong here? -- Thom -- 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] [ADMIN] pg_dump : no tables were found.
On Tue, Mar 6, 2012 at 7:22 AM, Piyush Lenka wrote: > Hi, > > I m trying to take backup of data of a particular table using pg_dump. > I used double quotes for table name but output is : > pg_dump : no tables were found. > > Command used : > -h localhost -p 5432 -U postgres -W -F p -a -t '"TestTable"' -f > DbBackup/BackupTableActions.sql TestDataBase > > This problem only shows when there is a upper case character in my table > name. > Please Help > > Thanks & Regards > Piyush > Hi You can try -t '"TestTable"' or -t \"TestTable\"
Re: [GENERAL] Lost data Folder, but have WAL files--- How to recover the database ?? Windows
On Tue, Mar 6, 2012 at 2:38 AM, chinnaobi wrote: > Recently i was doing streaming replication, I lost the data folder on both > the servers and left with WAL archives (some how). > > Can any one tell me how to recover database with WAL archives. > > Thanks in advance. There are no standard tools to help in those situations, but my company has performed many difficult recoveries where the database has been partially or severely damaged; references are available. Partial or full data recovery is often possible but obviously there is a cost and I respect community members who choose not to take that route. -- Simon Riggs http://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
