On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin
wrote:
> 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 utilizatio
Hi,
I have a script, that does, daily:
pg_ctl -D /data1 -s -m fast stop # data1 is working, rw database
pg_ctl -D /data2 -s -m fast stop # data2 is SR slave for another database
rsync -a --delete /data2/ /data1/
rm -rf /data1/pg_log/*
\print -u1 "$(date '+%Y-%m-%d %T %Z'): Pg_controldata for /data
Hi all,
I'm just wondering what the numbers really mean. The output is "2FA/B3AFB890",
for example. Do these numbers represent something meaningful I can use? Bytes,
offsets within files, what? How do you monitor the difference between
write_location and replay_location? It seems obvious the
On Wed, Mar 7, 2012 at 8:12 PM, Joshua D. Drake wrote:
>
>> "check_postgres --action=bloat" returns OK [after VACUUM FULL]. So it's not
>> bloat.
>> What else could it be?
>
> I would have to double check but I think
> check_postgres --action=bloat only checks for dead space, not usable space,
>
(2012/03/08 6:16), Joe Abbate wrote:
> Does that make sense? And if so, will it make sense in the future
> (considering potential FDW developments)?
I think that makes, and will make sense. Because SQL/MED standard
mentions about schema for only foreign table in "4.12 SQL-schemas" section.
FYI,
Dear Joshua,
You wrote:
> Try disabling replication on that table and clustering the table and then
> re-enabling
> replication.
...
> Alternatively you could disable replication on that table, truncate the
> table, and then
> re-enable replication for that table. A concern would be is that i
On 03/07/2012 06:27 PM, Aleksey Tsalolikhin wrote:
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 ran VACUUM FULL on this table, but it is still over 500 GB in
On Tue, Mar 6, 2012 at 7:05 PM, Aleksey Tsalolikhin
wrote:
> 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
On Wed, Mar 7, 2012 at 3:09 PM, Andy Colson wrote:
> Took me a while to figure this out, thought I'd paste it here for others to
> use:
>
> create or replace function round_timestamp(timestamp, integer) returns
> timestamp as $$
> select date_trunc('minute', $1) + cast(round(date_part('seco
On Thu, Feb 9, 2012 at 12:57 AM, Achilleas Mantzios
wrote:
> On Πεμ 09 Φεβ 2012 02:32:37 Aleksey Tsalolikhin wrote:
>> I got an alert from check_postgres.pl today on a long-running query on
>> our production database, but our PostgreSQL 8.4.9 server log, which is
>> configured to log queries over
Hi,
We've been discussing the subject in the pyrseas-general ML, but I think
it would be beneficial to get feedback from a broader audience.
The Pyrseas dbtoyaml utility outputs the objects in YAML, which ends up
looking like a tree (see
http://pyrseas.readthedocs.org/en/latest/dbtoyaml.html ) an
Hi,
I have a question regarding the following snippet in the official Postgres
documentation:
"If for some reason autovacuum fails to clear old XIDs from a table, the
system will begin to emit warning messages like this when the database's
oldest XIDs reach ten million transactions from the wrapa
On 03/07/2012 10:51 AM, mgo...@isstrucksoftware.net wrote:
I am trying to convert stored procedures from SQL Anywhere to Postgres.
I am getting error
ERROR: syntax error at or near "return"
LINE 2: return integer AS
^
** Error **
ERROR: syntax error at or near "retur
Works perfect. I'll trap and show only our databases to the user.
all thanks
On Wed, 2012-03-07 at 21:00 +0100, Szymon Guz wrote:
> On 7 March 2012 20:36, Bret Stern
> wrote:
> Is it possible through ODBC to connect to a PostgreSql
> server and query for the available databases?
>
On Wed, Mar 7, 2012 at 2:31 PM, Tom Lane wrote:
> Merlin Moncure writes:
>> On Wed, Mar 7, 2012 at 11:45 AM, Mike Blackwell
>> wrote:
>>> alter table a add column even_more_stuff boolean not null default false;
>
>> aha! that's not what you posted last time. you appended 'not null
>> default f
Excerpts from Tom Lane's message of mié mar 07 17:31:32 -0300 2012:
> This probably could be done for rowtype columns as well, but nobody has
> collected the necessary round tuits. I think there was some fear of
> locking/deadlock issues, too.
It's probably easy to do if you require it to be ma
Merlin Moncure writes:
> On Wed, Mar 7, 2012 at 11:45 AM, Mike Blackwell
> wrote:
>> alter table a add column even_more_stuff boolean not null default false;
> aha! that's not what you posted last time. you appended 'not null
> default false'; which inexplicably breaks the ALTER.
> try this:
On Wed, Mar 7, 2012 at 1:17 PM, Mike Blackwell wrote:
> As a followup, the workaround fails if there is data in the source table due
> to the initial null value placed in the existing data rows.
>
> [wcs1459@aclnx-cisp01 ~]$ psql --port=5433 -e -f x
> begin;
> BEGIN
> create table a (
> id seria
On 03/07/12 11:20 AM, Carlos Mennens wrote:
SCRIPTPATH=$(cd ${0%/*}&& pwd -P)
thats line 7 by my count.
thats some very strange stuff there. AFAIK ${0%/*} means, take $0
which is the name of the invoking command ('sh' in the invocation you
gave), match and remove the results of the patte
On 7 March 2012 20:36, Bret Stern wrote:
> Is it possible through ODBC to connect to a PostgreSql
> server and query for the available databases?
>
> When our application upgrades, we typically create a
> new database. I want to design a form which allows
> the user to select the old database, wh
Is it possible through ODBC to connect to a PostgreSql
server and query for the available databases?
When our application upgrades, we typically create a
new database. I want to design a form which allows
the user to select the old database, which then
migrates data to the new (currently connected
On Wed, Mar 7, 2012 at 12:51 PM, wrote:
> I am trying to convert stored procedures from SQL Anywhere to Postgres.
> I am getting error
>
> ERROR: syntax error at or near "return"
> LINE 2: return integer AS
> ^
>
>
> ** Error **
>
> ERROR: syntax error at or near "return"
On Wed, Mar 7, 2012 at 2:08 PM, John R Pierce wrote:
> whats that pg_backup.sh script look like? you're getting shell errors on
> line 7 and 8 of the script.
I didn't attach it since it's the same as the link I referenced in my
original post. I'll attach it in here for those that don't or can't
On 03/07/12 10:33 AM, Carlos Mennens wrote:
carlos@db1:~/postgresql$ sh pg_backup.sh
pg_backup.sh: 7: cd: can't cd to pg_backup.sh
pg_backup.sh: 8: pg_backup.sh: source: not found
pg_backup.sh: 16: [: !=: unexpected operator
Making backup directory in 2012-03-07/
pg_backup.sh: 54: pg_backup.sh: B
In a previous discussion thread, Oleg suggested that ts_rank is unable to
use GIN indices:
http://archives.postgresql.org/pgsql-general/2011-07/msg00351.php
This is the only information I have about this.
On Wed, Mar 7, 2012 at 18:59, Andrey Chursin wrote:
> Is there any way to sort by ranking,
On Wed, Mar 7, 2012 at 12:33 PM, Carlos Mennens
wrote:
> I'm trying to get the automated backup scripts to work from:
>
> http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux
>
> Currently I'm using PostgreSQL 9.1.3 and have the following three files:
>
> carlos@db1:~/postgresql$ ls -l
> tota
Reopening a six months old discussion... Is there any plans to improve
ts_rank performance by making it able to use GIN/GIST indices?
>From re-reading Oleg answers, I understand FTS indices do not contain
enough information for ts_rank. Because of that, ts_rank has to read the
ts_vector, which is
I am trying to convert stored procedures from SQL Anywhere to Postgres.
I am getting error
ERROR: syntax error at or near "return"
LINE 2: return integer AS
^
** Error **
ERROR: syntax error at or near "return"
SQL state: 42601
Character: 81
when I run this. I'm not
In response to Andrey Chursin :
> Hello.
> My application need to set advisory lock on UUID key, almost like it
> does pg_advisory_xact_lock function. The problem is argument type of
> this function - it consumes 8-byte value, not 16-byte.
>
> I can not lock on any(hi, low or middle) 8-byte part
I'm trying to get the automated backup scripts to work from:
http://wiki.postgresql.org/wiki/Automated_Backup_on_Linux
Currently I'm using PostgreSQL 9.1.3 and have the following three files:
carlos@db1:~/postgresql$ ls -l
total 20
drwxr-xr-x 2 carlos users 4096 Mar 7 13:13 backup
-rw-r--r-- 1
- Original Message -
> I mostly agree, but it's possible you got hit by a fixed bug.
I'm lucky ;)
someone know how I can correct the catalog and drop the old/renamed table?
Or probably is simpler:
- pg_dump database
- DROP database
- CREATE DATABASE
- psql database < dump
Or do I need t
On Tue, Mar 6, 2012 at 6:34 PM, Matteo Sgalaberni wrote:
> - 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
Is there any way to sort by ranking, avoiding seq scan?
The only way i see now is to use pg_trgm instead of ts_rank, but we
did not check yet how applicable is it for our purposes.
7 марта 2012 г. 20:53 пользователь Nicolas Grilly
написал:
> According to Oleg in a previous discussion, ts_rank doe
Hello.
My application need to set advisory lock on UUID key, almost like it
does pg_advisory_xact_lock function. The problem is argument type of
this function - it consumes 8-byte value, not 16-byte.
I can not lock on any(hi, low or middle) 8-byte part of UUID, as far
as it can produce unexpected
On Wed, Mar 7, 2012 at 11:45 AM, Mike Blackwell wrote:
>
> works for me -- what version are you on?
>
> merlin
>
> --
>
> [wcs1459@aclnx-cisp01 ~]$ psql --version
> psql (PostgreSQL) 9.1.1
> contains support for command-line editing
>
>
> [wcs1459@aclnx-cisp01 ~]$ cat x
> create table a (
>
>
>
> works for me -- what version are you on?
>
> merlin
>
> --
>
> [wcs1459@aclnx-cisp01 ~]$ psql --version
> psql (PostgreSQL) 9.1.1
> contains support for command-line editing
>
>
> [wcs1459@aclnx-cisp01 ~]$ cat x
> create table a (
> id serial,
> stuff text,
> more_stuff text
> );
>
On Wed, Mar 7, 2012 at 11:22 AM, Mike Blackwell wrote:
> Given a pair of tables:
>
> create table a (
> id serial,
> stuff text,
> more_stuff text
> );
>
> create table a_audit (
> id serial,
> old_record a,
> new_record a
> );
>
> How can one alter the structure of table a? Attemptin
Given a pair of tables:
create table a (
id serial,
stuff text,
more_stuff text
);
create table a_audit (
id serial,
old_record a,
new_record a
);
How can one alter the structure of table a? Attempting
ALTER TABLE a ADD COLUMN even_more_stuff text;
results in the message:
ERROR:
writes:
> De : Tom Lane [mailto:t...@sss.pgh.pa.us]
>> You might try reindexing pg_trigger before doing anything more invasive,
>> just in case the tuple is there but it's not being found because of a
>> messed-up index.
> Well, at some point we tried "REINDEX DATABASE pnssi_profiles_bench;"
>
According to Oleg in a previous discussion, ts_rank does not use index
because index does not store enough information for ranking:
http://archives.postgresql.org/pgsql-general/2011-07/msg00351.php
On Sat, Feb 18, 2012 at 12:39, Andrey Chursin wrote:
> Hello
>
> I have two unrelated questions ab
Martin Gregorie writes:
> On Wed, 2012-03-07 at 10:50 -0500, Tom Lane wrote:
>> Oh, time for a new theory then. What's the *exact* error message you
>> were getting from pg_dump? Do you get the same from a manual attempt to
>> connect to template1?
> Yes:
> # psql -d template1 -U postgres
> ps
De : Tom Lane [mailto:t...@sss.pgh.pa.us]
> > pnssi_profiles_bench=# drop SCHEMA _pnssi_slony_bench_profiles_01_110
> cascade;
> > ERROR: could not find tuple for trigger 37463634
>
> You might try reindexing pg_trigger before doing anything more invasive,
> just in case the tuple is there but it
Martin Gregorie writes:
> I've just logged in under postgres and run '\l' - and the databases
> postgres, template0 and template1 are still there:
Oh, time for a new theory then. What's the *exact* error message you
were getting from pg_dump? Do you get the same from a manual attempt to
connect
On 03/07/2012 09:43 AM, Marti Raudsepp wrote:
On Wed, Mar 7, 2012 at 16:23, Martin Gregorie wrote:
Is it possible to reinstate 'template1' and all its works without
reinitialising the database from scratch. It was suggested to me that
running "initdb" might fix it while leaving my data in place
On Wednesday, March 07, 2012 7:19:51 am Martin Gregorie wrote:
> On Wed, 2012-03-07 at 09:38 -0500, Gary Chambers wrote:
> > Martin,
> >
> > > 6) The next scheduled backup using pg_dumpall failed immediately
> > > because it couldn't find 'template1'.
> >
> > The template1 database is the default
On Wednesday, March 07, 2012 6:23:42 am Martin Gregorie wrote:
> Question
>
> Is it possible to reinstate 'template1' and all its works without
> reinitialising the database from scratch. It was suggested to me that
> running "initdb" might fix it while leaving my data in place though my
On Wed, 2012-03-07 at 09:38 -0500, Gary Chambers wrote:
> Martin,
>
> > 6) The next scheduled backup using pg_dumpall failed immediately because
> > it couldn't find 'template1'.
>
> The template1 database is the default database to which pg_dumpall attempts
> to connect. If you use the -l or --
writes:
> Short and simple: I'm trying to do the following:
> postgres@master-db01:~$ psql pnssi_profiles_bench
> psql (9.0.3)
> Type "help" for help.
> pnssi_profiles_bench=# drop SCHEMA _pnssi_slony_bench_profiles_01_110 cascade;
> ERROR: could not find tuple for trigger 37463634
You might
On Wednesday, March 07, 2012 6:23:42 am Martin Gregorie wrote:
>
> Question
>
> Is it possible to reinstate 'template1' and all its works without
> reinitialising the database from scratch. It was suggested to me that
> running "initdb" might fix it while leaving my data in place though
Took me a while to figure this out, thought I'd paste it here for others
to use:
create or replace function round_timestamp(timestamp, integer) returns
timestamp as $$
select date_trunc('minute', $1) + cast(round(date_part('seconds',
$1)/$2)*$2 || ' seconds' as interval);
$$ language sql imm
Marti,
As far as I can tell, the only way to remove the template1 database is to
rename it.
Templates are databases with the datistemplate column set to true. A
superuser can:
UPDATE pg_database SET datistemplate=false where datname='blah';
DROP DATABASE blah;
As far as Pg is concerned, the
On Wed, Mar 7, 2012 at 16:23, Martin Gregorie wrote:
> Is it possible to reinstate 'template1' and all its works without
> reinitialising the database from scratch. It was suggested to me that
> running "initdb" might fix it while leaving my data in place though my
> source was uncertain about its
Martin,
6) The next scheduled backup using pg_dumpall failed immediately because
it couldn't find 'template1'.
The template1 database is the default database to which pg_dumpall attempts
to connect. If you use the -l or --database option, you can change that and
pg_dumpall will resume functio
In the course of migrating from (I think) Postgres 8.4 under Fedora 12
to Postgres 9.1 under Fedora 16 I managed to loose 'template1' and
associated data. The immediate effect is that, although my schema and
data seem to be intact and are working correctly, pg_dumpall refused to
run because it can'
On Wed, Mar 7, 2012 at 6:34 AM, David Johnston wrote:
> I know there is currently work ongoing regarding normalizing SQL statements
> for logging purposes but has anyone considered given us the ability to name
> our statements.
>
> SELECT
> FROM ...
> WHERE
> NAMEAS 'Name to track by'
De : pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] De la part de mdione@orange.com
> What I understand is that it seems that somehow I got some references to
> objects
> that do not exist anymore, so I think I should run the equivalent of fsck I
> this
> da
I know there is currently work ongoing regarding normalizing SQL statements for
logging purposes but has anyone considered given us the ability to name our
statements.
SELECT
FROM ...
WHERE
NAMEAS 'Name to track by'
If a query lacks a name the algorithm generated normalized form would
On Wed, 2012-03-07 at 14:19 +0400, Sergey Konoplev wrote:
> On Tue, Mar 6, 2012 at 11:30 PM, Andre Lopes wrote:
> > This is the plPgSQL code that I need to write in Python. It is
> > possible to do this without using PlPgSQL?
>
Have you looked at pyodbc?
ODBC will usually accept statements allow
De : pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] De la part de mdione@orange.com
> I don't have the rest of the info (the entry in pg_class with OID 1259)
> because right now I'm running a preventive VACUUM, which it's taking ages.
Ok, the vacuum finishe
On Tue, Mar 6, 2012 at 11:30 PM, Andre Lopes wrote:
> This is the plPgSQL code that I need to write in Python. It is
> possible to do this without using PlPgSQL?
Do you want it to be done using pure SQL or you do not want to wrap it
into a stored function?
In the second case look at the DO comma
Short and simple: I'm trying to do the following:
postgres@master-db01:~$ psql pnssi_profiles_bench
psql (9.0.3)
Type "help" for help.
pnssi_profiles_bench=# drop SCHEMA _pnssi_slony_bench_profiles_01_110 cascade;
ERROR: could not find tuple for trigger 37463634
As you can see, this is a s
My company is in the process of migrating to a new pair of servers, running 9.1.
The database performance monetary transactions, we require
synchronous_commit on for all transactions.
Fusion-io is being considered, but will it give any significant
performance gain compared to normal SATA-based SS
62 matches
Mail list logo
