Re: [ADMIN] Why sequence grant is separated from table?

[Rural Hunter]

  
  
Yes, that's also an acceptable
  solution.
  
  于 2013/6/20 3:48, Craig James 写道:


  
  On Wed, Jun 19, 2013 at 2:35 AM, Rural
Hunter 
wrote:

  I really hate the error "permission denied for sequence x"
  when I grant on a table but forget to grant additionally on
  the related sequence to users. Can the permission of table and
  related sequences be merged?


  You asked this question back in March; here's what I suggested
  at the time:
  On Thu, Mar 28, 2013 at 10:56 PM, Rural Hunter wrote:
> Hi,
>
> I encounter the same issue often: Granted update/insert to an user but
> forgot to grant it on the related sequence. It's hard to understand that an
> user has write access on table but not on necessary sequences. I think the
> grant on tables should cascade to related sequences. What do you think?
>

Wouldn't it make more sense for the grant on the table to fail with an
appropriate error message?  That would solve your problem, and it wouldn't
be making security assumptions.  Cascading permissions seems like a recipe
for trouble.

Craig

  I suggest is that having the "grant ... on tablename" fail
  would serve your purpose.  What you want is for it to let you
  know you've made a security change that is bound to fail.  I
  think it would actually be better to have the GRANT fail since
  it would notify you that the script or procedure you are using
  is incorrect.
  
  Craig
   


  
  
  -- 
  Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
  To make changes to your subscription:
  http://www.postgresql.org/mailpref/pgsql-admin

  
  


  

[ADMIN] excessive WAL activity

[Sean Dillon]

Just turned on WAL archiving to an S3 bucket for a small database - total size 
of perhaps 2-4G.  After turning on achiving, we're seeing WAL logs written to 
S3 at the rate of about 1G every 3 minutes.  That seems completely unreasonable 
given usage of the db.  I can even see that nearly nothing is happening with 
this:

select datname, usename, procpid, client_addr, waiting, query_start, 
current_query from pg_stat_activity;

Nearly every time I run that, all 20 connections have current_query = ''. 
 Does current_query include inserts, updates, and deletes or just select 
statements?

Any ideas what to look for or how to solve this?



-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] pg_upgrade issue

[Marcos Cano]

yes i guess is postgis... because we are using postgis in our DB but I
wasn't aware

On Wednesday, June 19, 2013, Bruce Momjian wrote:

> On Wed, Jun 19, 2013 at 11:37:30AM -0600, Marcos Cano wrote:
> > ERROR:  could not access file "$libdir/liblwgeom": No such file or
> directory
> > STATEMENT:  LOAD '$libdir/liblwgeom'
> > command: "/usr/local/pgsql9.2.4/bin/pg_ctl" -w -D
> "/usr/local/pgsql9.2.4/data/"
> > -o "" -m fast stop >> "pg_upgrade_server.log" 2>&1
>
> OK, so your database references '$libdir/liblwgeom', but the file does
> not exist in the 9.2.4 lib directory.  Odds are it is some Postgres
> extension that you need to install in the new cluster.
>
> --
>   Bruce Momjian  >
> http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
>   + It's impossible for everything to be true. +
>

Re: [ADMIN] WAL scenario valid?

[Sergey Konoplev]

On Tue, Jun 18, 2013 at 11:20 PM, prakhar jauhari  wrote:
> When a switchover happens DB2 becomes the new master and when DB1 comes up
> it will act as the standby to the new master (.history files from new master
> are copied to DB1 to bump up its timeline so as to setup SR). DB1 is not
> recreated from scratch. This runs fine in normal switchover, but there seems

I am not sure this works fine. You have probably got some silent
corruption on db1.

What you need is to resync db1 from db2. You can do it either the
canonical way, base backup with pg_basebackup or rsync, or using
pg_rewind.

>From pg_rewind docs:

pg_rewind is a tool for synchronizing a PostgreSQL data directory with another
PostgreSQL data directory that was forked from the first one. The result is
equivalent to rsyncing the first data directory (referred to as the old cluster
from now on) with the second one (the new cluster). The advantage of pg_rewind
over rsync is that pg_rewind uses the WAL to determine changed data blocks,
and does not require reading through all files in the cluster. That makes it
a lot faster when the database is large and only a small portion of it differs
between the clusters.

https://github.com/vmware/pg_rewind

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray...@gmail.com


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Why sequence grant is separated from table?

[Craig James]

On Wed, Jun 19, 2013 at 2:35 AM, Rural Hunter  wrote:

> I really hate the error "permission denied for sequence x" when I
> grant on a table but forget to grant additionally on the related sequence
> to users. Can the permission of table and related sequences be merged?
>

You asked this question back in March; here's what I suggested at the time:

On Thu, Mar 28, 2013 at 10:56 PM, Rural Hunter wrote:
> Hi,
>
> I encounter the same issue often: Granted update/insert to an user but
> forgot to grant it on the related sequence. It's hard to understand that an
> user has write access on table but not on necessary sequences. I think the
> grant on tables should cascade to related sequences. What do you think?
>

Wouldn't it make more sense for the grant on the table to fail with an
appropriate error message?  That would solve your problem, and it wouldn't
be making security assumptions.  Cascading permissions seems like a recipe
for trouble.

Craig

I suggest is that having the "grant ... on tablename" fail would serve your
purpose.  What you want is for it to let you know you've made a security
change that is bound to fail.  I think it would actually be better to have
the GRANT fail since it would notify you that the script or procedure you
are using is incorrect.

Craig


>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin
>

Re: [ADMIN] PG_UPGRADE major issue

[Burgholzer, Robert (DEQ)]

This error:
"ERROR:  could not access file "$libdir/liblwgeom": No such file or directory"

Means that you have PostGIS installed and it can't find it in the upgrade path 
I believe.  Did you get postGI configured correctly in the upgraded version?

r.b.



-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] PG_UPGRADE major issue

[Marcos Cano]

so i'm trying to migrate/upgrade from 8.3.2 to 9.2.4 and following the
instructions of the documentation i've found myself in a major issue which
has overwhelmed my little knowledge.
these were the steps i followed:

1)downloand and install 9.2.4, with the contrib packages.
2)init the 9.2.4 cluster in /usr/local/pgsql9.2.4/data, i also have the
8.3.2 cluster in /usr/local/pgsql/data
3) stop both servers 
4)change the 9.2.4 port to 5433
5) i run this command:

/usr/local/pgsql9.2.4/bin/./pg_upgrade -b /usr/local/pgsql/bin/ -B
/usr/local/pgsql9.2.4/bin/ -c -d /usr/local/pgsql/data/ -D
/usr/local/pgsql9.2.4/data/ -p 5432 -P 5433 -v


so after some 30 seconds the upgrade process seemed like "working"  but
after it crashed
with this message:
Checking for presence of required libraries fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
loadable_libraries.txt

Failure, exiting
"/usr/local/pgsql9.2.4/bin/pg_ctl" -w -D "/usr/local/pgsql9.2.4/data/" -o ""
-m fast stop >> "pg_upgrade_server.log" 2>&1


so i went to check the log and found:


ERROR:  could not access file "$libdir/liblwgeom": No such file or directory
STATEMENT:  LOAD '$libdir/liblwgeom'
command: "/usr/local/pgsql9.2.4/bin/pg_ctl" -w -D
"/usr/local/pgsql9.2.4/data/" -o "" -m fast stop >> "pg_upgrade_server.log"
2>&1
LOG:  received fast shutdown request
LOG:  aborting any active transactions
waiting for server to shut downFATAL:  terminating connection due to
administrator command
LOG:  could not send data to client: Broken pipe
LOG:  shutting down
LOG:  database system is shut down
 done
server stopped


please help as i dont have any idea what's going wrong




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PG-UPGRADE-major-issue-tp5759906.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] pg_upgrade issue

[Bruce Momjian]

On Wed, Jun 19, 2013 at 11:37:30AM -0600, Marcos Cano wrote:
> ERROR:  could not access file "$libdir/liblwgeom": No such file or directory
> STATEMENT:  LOAD '$libdir/liblwgeom'
> command: "/usr/local/pgsql9.2.4/bin/pg_ctl" -w -D 
> "/usr/local/pgsql9.2.4/data/"
> -o "" -m fast stop >> "pg_upgrade_server.log" 2>&1

OK, so your database references '$libdir/liblwgeom', but the file does
not exist in the 9.2.4 lib directory.  Odds are it is some Postgres
extension that you need to install in the new cluster.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] pg_upgrade issue

[Marcos Cano]

so i'm trying to migrate/upgrade from 8.3.2 to 9.2.4 and following the
instructions of the documentation i've found myself in a major issue which
has overwhelmed my little knowledge.
these were the steps i followed:

1)downloand and install 9.2.4, with the contrib packages.
2)init the 9.2.4 cluster in /usr/local/pgsql9.2.4/data, i also have the
8.3.2 cluster in /usr/local/pgsql/data
3) stop both servers
4)change the 9.2.4 port to 5433
5) i run this command:

/usr/local/pgsql9.2.4/bin/./pg_upgrade -b /usr/local/pgsql/bin/ -B
/usr/local/pgsql9.2.4/bin/ -c -d /usr/local/pgsql/data/ -D
/usr/local/pgsql9.2.4/data/ -p 5432 -P 5433 -v


so after some 30 seconds the upgrade process seemed like "working"  but
after it crashed
with this message:
Checking for presence of required libraries fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
loadable_libraries.txt

Failure, exiting
"/usr/local/pgsql9.2.4/bin/pg_ctl" -w -D "/usr/local/pgsql9.2.4/data/" -o
"" -m fast stop >> "pg_upgrade_server.log" 2>&1


so i went to check the log and found:


ERROR:  could not access file "$libdir/liblwgeom": No such file or
directory
STATEMENT:  LOAD '$libdir/liblwgeom'
command: "/usr/local/pgsql9.2.4/bin/pg_ctl" -w -D
"/usr/local/pgsql9.2.4/data/" -o "" -m fast stop >> "pg_upgrade_server.log"
2>&1
LOG:  received fast shutdown request
LOG:  aborting any active transactions
waiting for server to shut downFATAL:  terminating connection due to
administrator command
LOG:  could not send data to client: Broken pipe
LOG:  shutting down
LOG:  database system is shut down
 done
server stopped


please help as i dont have any idea what's going wrong

Re: [ADMIN] Re: PG_UPGRADE issues "this utility can only upgrade to postgresql version 9.2."

[Ziggy Skalski]

On 13-06-19 10:16 AM, jmfox180 wrote:

so i tried to do a

/usr/local/pgsql.old/bin/postgres -D /usr/local/pgsql.old/data start

LOG:  database system was shut down at 2013-06-19 08:10:51 CST
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

it got stuck there ^^

the i tried to
/usr/local/pgsql.old/bin/psql -p 5432

and it turns that
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

so basically i just moved the whole pgsql8.3.2 that i had to a new folder
which is pgsql.old with sudo mv etc,etc
but i can not turn on the 8.3 server, i did try to start the new one (9.2.4)
and yes i can.

any idea?



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PG-UPGRADE-issues-this-utility-can-only-upgrade-to-postgresql-version-9-2-tp5759509p5759873.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.




Does the file /var/run/postgresql/.s.PGSQL.5432.lock exist on the system 
you're trying to upgrade ?   If so, what's inside (cat it), does it 
point to your /usr/local/pgsql.old/data directory?  I've found that in 
my (limited) experience with pg_upgrade, it's good to run the upgrade 
from /tmp directory, that way the lock file gets created there and 
usually there's no write permission problems in /tmp either :)


Ziggy

Re: [ADMIN] PG_UPGRADE issues "this utility can only upgrade to postgresql version 9.2."

[Bruce Momjian]

On Mon, Jun 17, 2013 at 07:08:03PM -0400, Bruce Momjian wrote:
> On Mon, Jun 17, 2013 at 10:54:06AM -0700, jmfox180 wrote:
> > so i'm running ubuntu 12.04 with pg8.3.2 installed on it, i want to upgrade
> > to 9.2.4 using the pg_upgrade. i've followed this steps:
> > 
> > 1) download postgres9.2.4
> > 2)configure it and compile it
> > 3) make and install the pg_upgrade and pg_upgrade_support utilities from the
> > pg9.2.4 contrib folder
> > 4) init the pg9.2.4 cluster with:  initdb -D /usr/local/pgsql9.2.4/data
> > 5) try to check for compatibilities between the 2 clusters and getting the
> > following error while logged into postgres user account.
> > 
> > command>> 
> > /usr/local/pgsql9.2.0/bin/./pg_upgrade -b /usr/local/pgsql/bin/ -B
> > /usr/local/pgsql9.2.0/bin/ -c -d /usr/local/pgsql/data/ -D
> > /usr/local/pgsql9.2.0/data/ 
> > 
> > error>>
> > cannot write to log file pg_upgrade_internal.log
> > Failure, exiting
> > 
> > 6) so i decided to move on and just do an upgrade without the -c (check for
> > compatibilities) :
> > 
> > 6.1) stop both servers
> > 6.2) run the same command without the -c to upgrade and getting
> > :
> >" this utility can only upgrade to postgresql version 9.2"
> > 
> > please enlighten me or give me any advice.
> 
> You need write permission in the current directory.  My guess is that
> your /usr/local/pgsql9.2.0/data/ isn't really 9.2.

FYI, this is the same person posting as Marcos Cano.  I am working with
him via private email.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] PG_UPRADE issue

[jmfox180]

so when following the instructions of pg_upgrade

" If your installation directory is not version-specific, e.g.
/usr/local/pgsql, it is necessary to move the current PostgreSQL install
directory so it does not interfere with the new PostgreSQL installation.
Once the current PostgreSQL server is shut down, it is safe to rename the
PostgreSQL installation directory; assuming the old directory is
/usr/local/pgsql, you can do:

mv /usr/local/pgsql /usr/local/pgsql.old

to rename the directory." 

i was just curious and try to see if the current postgres (which now is in
the pgsql.old directory instead) is still working

so i did a 

/usr/loca/pgsql.old/bin/pg_ctl -D /usr/local/pgsql.old/data start
and it showed

LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
but did not go further than that


then i tried to /usr/local/pgsql8/bin/psql to connect to the server
and it says 

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?


so am i doing something wrong with the "mv"  command that i'm missing??
(beacuase it seems that everything screwed since i rename the complete
folder)







--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PG-UPRADE-issue-tp5759891.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] Re: PG_UPGRADE issues "this utility can only upgrade to postgresql version 9.2."

[jmfox180]

so i tried to do a 

/usr/local/pgsql.old/bin/postgres -D /usr/local/pgsql.old/data start

LOG:  database system was shut down at 2013-06-19 08:10:51 CST
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

it got stuck there ^^

the i tried to 
/usr/local/pgsql.old/bin/psql -p 5432

and it turns that 
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

so basically i just moved the whole pgsql8.3.2 that i had to a new folder
which is pgsql.old with sudo mv etc,etc
but i can not turn on the 8.3 server, i did try to start the new one (9.2.4)
and yes i can.

any idea?



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PG-UPGRADE-issues-this-utility-can-only-upgrade-to-postgresql-version-9-2-tp5759509p5759873.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Upgrading 9.0.5 to 9.0.13 on ubuntu 10.4

[Peter Eisentraut]

On 6/6/13 8:01 PM, Kasia Tuszynska wrote:
> dpkg: error processing 
> /var/cache/apt/archives/postgresql-9.0_9.0.13-1.pgdg10.4+1_amd64.deb 
> (--unpack):
>  trying to overwrite '/usr/share/locale/zh_TW/LC_MESSAGES/plpython-9.0.mo', 
> which is also in package postgresql-plpython-9.0 0:9.0.5-1~lucid
> dpkg-deb: subprocess paste killed by signal (Broken pipe)
> Errors were encountered while processing:
>  /var/cache/apt/archives/postgresql-9.0_9.0.13-1.pgdg10.4+1_amd64.deb

This appears to be a packaging bug.  You can probably work around the
issue by temporarily uninstalling the postgresql-plpython-9.0 package.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Concrete steps for use of PITR warm backup?

[francis picabia]

On Thu, Jun 13, 2013 at 10:51 AM, francis picabia  wrote:
> I think my needs are standard for PITR: single master server and single
> backup server, warm, with desire for PITR.
>
> I'm running postgres 8.4.
>
> I've looked at Postgres docs on warm backup, and several blogs
> attempting instructions to help.
>
> Everything I've seen does a good job of documenting the
> initial set up:
>
> - how to backup the postgres instance using rsync to second server
> - how to enable wal archiving with rsync to second server
>
> After that, the instructions are less clear.
>
> I've also checked out barman.  It also is good at describing
> the initial set up, but when it is time to describe PITR
> use, they defer to Postgres docs and training which they sell.
>
> Is there a good guide with examples on how to configure and
> *use* a PITR solution?
>
> I'm having problems with finding examples discussing:
>
> - how to start up backup postgres and incorporate wal files
> - how to maintain the build up of wal files (with explicit example)
> - whether to run (or not run) the backup postgres service
>
> I've made attempts to set up the backup, with failure.  It seems
> we need the WAL files in the pg_xlog directory, but postgres
> refuses to start.  It isn't surprising to me as I had to try
> rather than know.
>
> Postgres documentation is written with too many statements along the
> "If... Then..." format.  When you already know what to do, this
> format is sufficient.  When you are looking for a set of steps,
> even an example scenario, this is insufficient.
>
> I'd think the scenario of single master, single backup, PITR with warm
> recovery from WAL archive, is very typical and what many people
> are looking for.  Can't it be documented clearly with
> a complete set of steps?

I still think the documentation is lacking real life examples, but my
needs have been met by pgbarman.  It is very good, saves developing
a script yourself, and works at 4 AM when your brain is not.  I also
like that barman is so easy that I can go on vacation and anyone
can follow the instructions for recovery.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] pg_stop_backup is not archiving latest transaction log from pg_xlog directory

[girish R G peetle]

Thanks a lot Laurenz Albe.


On Wed, Jun 19, 2013 at 6:16 PM, Albe Laurenz wrote:

> girish R G peetle wrote:
> > I was wondering if I can use the modified version of your solution.
> >
> >  1. Convert output of pg_stop_backup to transaction log file name. (This
> refers to the latest active
> > transaction log file)
> >  2. If we are able to see this file under archive(WAL) directory, backup
> this file from this
> > directory.
> >  3. If the file is not found under archive(WAL) directory, backup this
> file form pg_xlog directory.
>
> That should work just fine.
>
> Yours,
> Laurenz Albe
>

Re: [ADMIN] pg_stop_backup is not archiving latest transaction log from pg_xlog directory

[Albe Laurenz]

girish R G peetle wrote:
> I was wondering if I can use the modified version of your solution.
> 
>  1. Convert output of pg_stop_backup to transaction log file name. (This 
> refers to the latest active
> transaction log file)
>  2. If we are able to see this file under archive(WAL) directory, backup this 
> file from this
> directory.
>  3. If the file is not found under archive(WAL) directory, backup this file 
> form pg_xlog directory.

That should work just fine.

Yours,
Laurenz Albe

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] pg_stop_backup is not archiving latest transaction log from pg_xlog directory

[girish R G peetle]

On Wed, Jun 19, 2013 at 4:32 PM, Albe Laurenz wrote:

> girish R G peetle wrote:
> > @Albe
> > Even I was thinking to backup the transaction log files under pg_xlog
> directory. But my concern is,
> > will it lead some kind of error or inconsistency ?
>
> No.
> During recovery, archived WAL files will overwrite the ones in
> pg_xlog.  The WAL files in pg_xlog will only be used if
> no archived version is available.
>
> As long as pg_xlog is backed up after the end of
> pg_stop_backup(), you should be safe.
>
> Yours,
> Laurenz Albe
>



Hi Laurenz Albe,
I was wondering if I can use the modified version of your solution.

 1. Convert output of pg_stop_backup to transaction log file name. (This
refers to the latest active transaction log file)
 2. If we are able to see this file under archive(WAL) directory, backup
this file from this directory.
 3. If the file is not found under archive(WAL) directory, backup this file
form pg_xlog directory.


Thanks
Girish

Re: [ADMIN] pg_stop_backup is not archiving latest transaction log from pg_xlog directory

[Albe Laurenz]

girish R G peetle wrote:
> @Albe
> Even I was thinking to backup the transaction log files under pg_xlog 
> directory. But my concern is,
> will it lead some kind of error or inconsistency ?

No.
During recovery, archived WAL files will overwrite the ones in
pg_xlog.  The WAL files in pg_xlog will only be used if
no archived version is available.

As long as pg_xlog is backed up after the end of
pg_stop_backup(), you should be safe.

Yours,
Laurenz Albe

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[ADMIN] Seemingly hanging checkpoint

[Dan Kogan]

Hello,

It seems that a checkpoint started but didn't finish last night on our db 
server.
We are using version 9.2.4.

This is from the postgres log:

2013-06-19 03:38:49 UTC [32252]: [19-1] [] LOG:  checkpoint starting: time
2013-06-19 03:55:01 UTC [32252]: [20-1] [] LOG:  checkpoint complete: wrote 
25408 buffers (4.8%); 0 transaction log file(s) added, 0 removed, 89 recycled; 
write=971.925 s, sync=0.495 s, total=972.440 s; sync files=901, longest=0.021 
s, average=0.000 s

2013-06-19 04:23:49 UTC [32252]: [21-1] [] LOG:  checkpoint starting: time


Seems like the checkpoint that started at 4:23 UTC did not complete.

Any advice what we can do to complete the checkpoint is appreciated.

Thanks,
Dan Kogan

Re: [ADMIN] pg_stop_backup is not archiving latest transaction log from pg_xlog directory

[girish R G peetle]

Hi
Thanks everyone for your quick response.

@Raghavendra,
Calling CHECKPOINT did not help. It will just force a transaction log
checkpoint, but it won't ensure/enforce archiving of current active
transaction log file.

@Albe
Even I was thinking to backup the transaction log files under pg_xlog
directory. But my concern is, will it lead some kind of error or
inconsistency ?

@Simon
Thanks for the confirmation. Unfortunately our client has more than 150
servers configured, and they are not willing to upgrade their servers.
Can you give some suggestions on what Albe has suggested  ( Archiving
transaction log files from pg_xlog directory after pg_stop_backup) ?


Thanks
Girish


On Wed, Jun 19, 2013 at 3:01 PM, Simon Riggs  wrote:

> On 19 June 2013 09:32, girish R G peetle  wrote:
>
> > Is there a way to switch the current transaction log file in Postgres
> 8.0 ?
>
> No, that was an addition to 8.2
>
> Suggest that you send more data until the WAL file switches.
>
> You should upgrade with some urgency.
>
> --
>  Simon Riggs   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [ADMIN] WAL scenario valid?

[prakhar jauhari]

Hi,

Ya the error logs came, but only when the DB1 was corrupted. Here is a
excerpt from the logs.

*LOG:  database system was shut down at 2013-06-19 09:40:55 UTC
LOG:  restored log file "0004.history" from archive
cp: cannot stat `/data/pgsql/archivedir/0005.history': No such file or
directory
LOG:  restored log file "0004.history" from archive
LOG:  entering standby mode
cp: cannot stat `/data/pgsql/archivedir/00040013': No such
file or directory
cp: cannot stat `/data/pgsql/archivedir/00030013': No such
file or directory
LOG:  consistent recovery state reached at 0/1380
LOG:  record with zero length at 0/1380
LOG:  database system is ready to accept read only connections
cp: cannot stat `/data/pgsql/archivedir/00040013': No such
file or directory
cp: cannot stat `/data/pgsql/archivedir/00030013': No such
file or directory
cp: cannot stat `/data/pgsql/archivedir/0005.history': No such file or
directory
LOG:  streaming replication successfully connected to primary
ERROR:  cannot execute CREATE ROLE in a read-only transaction
STATEMENT:  CREATE USER replicationuser REPLICATION;
LOG:  invalid record length at 0/1380
FATAL:  terminating walreceiver process due to administrator command
cp: cannot stat `/data/pgsql/archivedir/00040013': No such
file or directory
cp: cannot stat `/data/pgsql/archivedir/00040013': No such
file or directory
cp: cannot stat `/data/pgsql/archivedir/0005.history': No such file or
directory
LOG:  invalid record length at 0/1380
*

This is what happened..
When DB1 came up as a new standby, It connected SR with DB2 (new master)
then after some time it terminated the walreceiver process.
And the invalid record length logs went on, until i took base backup on DB1
and restarted it.


Also On DB1 (before DB1 was started in standby mode)  : pg_controldata
/data/pgsql/9.2/data/

pg_control version number:922
Catalog version number:   201204301
Database system identifier:   5891091665573732008
Database cluster state:   shut down
pg_control last modified: Wed Jun 19 09:40:55 2013
Latest checkpoint location:   0/1320
Prior checkpoint location:0/124F1BC0
Latest checkpoint's REDO location:0/1320
Latest checkpoint's TimeLineID:   3
Latest checkpoint's full_page_writes: off
Latest checkpoint's NextXID:  0/8409
Latest checkpoint's NextOID:  18470
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:669
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Time of latest checkpoint:Wed Jun 19 09:40:54 2013
Minimum recovery ending location: 0/0
Backup start location:0/0
Backup end location:  0/0
End-of-backup record required:no
Current wal_level setting:hot_standby
Current max_connections setting:  300
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:   8
Database block size:  8192
Blocks per segment of large relation: 131072
WAL block size:   8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index:  32
Maximum size of a TOAST chunk:1996
Date/time type storage:   64-bit integers
Float4 argument passing:  by value
Float8 argument passing:  by value


Will it be wrong to compare* the "last replayed xlog id" on DB2* (which is
the new master) with "Latest checkpoint location" on DB1 before starting
DB1 in standby mode and if "Latest checkpoint location" on DB1(old master)
is greater than *"last replayed xlog id*" on DB2(new master) then i have to
go for basebackup.


regards,
Prakhar.


On Wed, Jun 19, 2013 at 1:11 PM, Albe Laurenz wrote:

> prakhar jauhari wrote:
> > I am facing a similar kind of problem, but in a two node setup.
> Streaming replication is being used
> > with a high wal_keep_segments, using log shipping to bump up timelines
> on the standby server to setup
> > SR(streaming replication).
> > DB1 - master
> >
> > DB2 - standby
> >
> >
> > When a switchover happens DB2 becomes the new master and when DB1 comes
> up it will act as the standby
> > to the new master (.history files from new master are copied to DB1 to
> bump up its timeline so as to
> > setup SR). DB1 is not recreated from scratch. This runs fine in normal
> switchover, but there seems to
> > be problem in the following situation, leading to database corruption:
> >
> >
> > Current state :
> > DB1 - master
> > DB2 - standby
> >
> >
> > Now the failing scenario:
> >
> >
> >
> > 1. DB2 machine goes down.
> >
> > 2. After some time DB1 machine also goes down (DB2 is still down).
> >
> > 3. Now DB2 comes up (it will jo

Re: [ADMIN] Why sequence grant is separated from table?

[Szymon Guz]

On 19 June 2013 12:06, Rural Hunter  wrote:

>  于 2013/6/19 17:47, Szymon Guz 写道:
>
> On 19 June 2013 11:35, Rural Hunter  wrote:
>
>> I really hate the error "permission denied for sequence x" when I
>> grant on a table but forget to grant additionally on the related sequence
>> to users. Can the permission of table and related sequences be merged?
>>
>>
>  I can imagine a situation where you want a user to be granted rights on
> a table, to use sequence with curval and nextval functions, but not setval,
> as it can provide some database problems.
>
> oh, is it possible to separately grant nextval and setval on sequence
> currently?
>
>
According to http://www.postgresql.org/docs/9.2/static/sql-grant.html

SELECTFor sequences, this privilege also allows the use of the currval
function.

UPDATEFor sequences, this privilege allows the use of the nextval and setval
 functions.

>
>  What's more, in general, you cannot say which sequence is used for which
> table, if the sequence is not owned by the table.
>
> Can we get it from the column definition such as a serial column?
>
>
Sure we can.


>
>  Which permissions of a table do you want to merge with which permissions
> of sequences?
>
> At least granting insert on a table means to grant update on the sequence
> used by serial columns.
>

It could be done. Unfortunately it doesn't cover all cases, like some ORMs
which can use one sequence for many different tables, but of course for
serials it can be done.

Szymon

Re: [ADMIN] Why sequence grant is separated from table?

[Rural Hunter]

  
  
于 2013/6/19 17:47, Szymon Guz 写道:


  On 19 June 2013 11:35, Rural Hunter 
wrote:

  
I really
  hate the error "permission denied for sequence x" when
  I grant on a table but forget to grant additionally on the
  related sequence to users. Can the permission of table and
  related sequences be merged?
  



I can imagine a situation where you want a
  user to be granted rights on a table, to use sequence with
  curval and nextval functions, but not setval, as it can
  provide some database problems.
  

  

oh, is it possible to separately grant nextval and setval on
sequence currently?

  

  


What's more, in general, you cannot say which
  sequence is used for which table, if the sequence is not
  owned by the table.
  

  

Can we get it from the column definition such as a serial column?

  

  


Which permissions of a table do you want to
  merge with which permissions of sequences?
  

  

At least granting insert on a table means to grant update on the
sequence used by serial columns.

  

  


regards
Szymon
  

  


  

Re: [ADMIN] Why sequence grant is separated from table?

[Szymon Guz]

On 19 June 2013 11:35, Rural Hunter  wrote:

> I really hate the error "permission denied for sequence x" when I
> grant on a table but forget to grant additionally on the related sequence
> to users. Can the permission of table and related sequences be merged?
>
>
I can imagine a situation where you want a user to be granted rights on a
table, to use sequence with curval and nextval functions, but not setval,
as it can provide some database problems.

What's more, in general, you cannot say which sequence is used for which
table, if the sequence is not owned by the table.

Which permissions of a table do you want to merge with which permissions of
sequences?

regards
Szymon

[ADMIN] Why sequence grant is separated from table?

[Rural Hunter]

I really hate the error "permission denied for sequence x" when I 
grant on a table but forget to grant additionally on the related 
sequence to users. Can the permission of table and related sequences be 
merged?



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] pg_stop_backup is not archiving latest transaction log from pg_xlog directory

[Simon Riggs]

On 19 June 2013 09:32, girish R G peetle  wrote:

> Is there a way to switch the current transaction log file in Postgres 8.0 ?

No, that was an addition to 8.2

Suggest that you send more data until the WAL file switches.

You should upgrade with some urgency.

--
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] pg_stop_backup is not archiving latest transaction log from pg_xlog directory

[Raghavendra]

On Wed, Jun 19, 2013 at 2:02 PM, girish R G peetle
wrote:

> Hi
> The question is regrading PostgreSQL 8.0.26 version on Linux platform. I
> know that 8.0 version is no longer supported. But one of our customer is
> using this version.
> We are using File System based backup method to protect PostgreSQL server.
>
> In PostgreSQL 8.2 and above versions, I can see that latest transaction
> log will be archived when pg_stop_backup is executed.
>
> But in PostgreSQL 8.0 version this is not happening. A transaction log
> file gets archived only when it is completely filled.
> Due to this behavior, the changes made to the server during
> pg_start_backup and pg_stop_backup won't be captured in the archived
> transaction log.
>
Is there a way to switch the current transaction log file in Postgres 8.0 ?
>
> Thanks
>  Girish
>

Can you try CHECKPOINT before pg_stop_backup() and see whether it meets
your requirement ?

http://www.postgresql.org/docs/8.0/static/sql-checkpoint.html

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

[ADMIN] pg_stop_backup is not archiving latest transaction log from pg_xlog directory

[girish R G peetle]

Hi
The question is regrading PostgreSQL 8.0.26 version on Linux platform. I
know that 8.0 version is no longer supported. But one of our customer is
using this version.
We are using File System based backup method to protect PostgreSQL server.

In PostgreSQL 8.2 and above versions, I can see that latest transaction log
will be archived when pg_stop_backup is executed.

But in PostgreSQL 8.0 version this is not happening. A transaction log file
gets archived only when it is completely filled.
Due to this behavior, the changes made to the server during pg_start_backup
and pg_stop_backup won't be captured in the archived transaction log.

Is there a way to switch the current transaction log file in Postgres 8.0 ?

Thanks
Girish

Re: [ADMIN] WAL scenario valid?

[Albe Laurenz]

prakhar jauhari wrote:
> I am facing a similar kind of problem, but in a two node setup. Streaming 
> replication is being used
> with a high wal_keep_segments, using log shipping to bump up timelines on the 
> standby server to setup
> SR(streaming replication).
> DB1 - master
> 
> DB2 - standby
> 
> 
> When a switchover happens DB2 becomes the new master and when DB1 comes up it 
> will act as the standby
> to the new master (.history files from new master are copied to DB1 to bump 
> up its timeline so as to
> setup SR). DB1 is not recreated from scratch. This runs fine in normal 
> switchover, but there seems to
> be problem in the following situation, leading to database corruption:
> 
> 
> Current state :
> DB1 - master
> DB2 - standby
> 
> 
> Now the failing scenario:
> 
> 
> 
> 1. DB2 machine goes down.
> 
> 2. After some time DB1 machine also goes down (DB2 is still down).
> 
> 3. Now DB2 comes up (it will join the cluster as master as DB1 is still down).
> 
> 4. DB2 is started as master postgresql.
> 
> 5. Now DB1 comes up (it will join the cluster as standby to DB2)
> 
> 6. Now when DB1 attempts to SR with DB2, DB1 gets corrupted.
> 
> 
> Looked into the issue and found that when DB1 went down initially, it created 
> some WAL's which were
> not synced to DB2 as it was already down.
> 
> Now when DB2 started as master it still had not played the last few WAL's 
> created by DB1(when it was
> master). DB2 starts as master properly.
> 
> When DB1 came as standby to DB2, it bumped it timeline using history file 
> from DB2, but when SR was
> setup with DB2, DB1 gets corrupted.
> 
> 
> Now the question is:
> 
> 
> 1. Is this a theoretically valid approach?
> 
> 2. If it is a valid approach, then how can i detect such a scenario (where SR 
> will corrupt the DB)? So
> that i can go for a basebackup in such situation.

If you want to use the old primary as new standby without a new backup,
you have to ascertain that all transactions from the former have
been replayed at the latter.

To figure out where the primary currently is, you can
   SELECT pg_current_xlog_location();

To figure how much the standby has replayed, you can
   SELECT pg_last_xlog_replay_location();

Of course this only works if both are up.

I think that it would be tricky to automatize that; I'd choose
making a new backup after each failover.

In the event of a controlled failover it might be an option.

I am surprised that the scenario you described leads to
corruption; I would have expected an error message.

Yours,
Laurenz Albe

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin