chris | 22 Dec 02:56 2013
Picon

BUG #8695: Reloading dump fails at COMMENT ON EXTENSION plpgsql

The following bug has been logged on the website:

Bug reference:      8695
Logged by:          Christian Ullrich
Email address:      chris <at> chrullrich.net
PostgreSQL version: 9.3.2
Operating system:   all
Description:        

A non-superuser cannot reload any dump of a database that contains the
plpgsql extension, because the dump unconditionally attempts to set the
comment on that extension. This fails because plpgsql is owned by the
superuser who installed it.

This contradicts the manual, which says: "The dumps produced by pg_dump are
relative to template0." The plpgsql extension is present in template0, with
the identical comment, and therefore neither extension nor comment should be
dumped at all. (I know this is splitting hairs, because pg_dump does not
actually compare the subject database to template0, but still, the
contradiction is there.)

The extension itself is dumped as CREATE IF NOT EXISTS, so that works, but
there is no conditional syntax for comments, and since pg_dump does not know
whether the comment has been changed from the default, it could not use one
anyway.

I can think of one possible fix (aside from simply filtering that line from
the dump): COMMENT could be a no-op if the requested comment is identical to
the existing one.

(Continue reading)

sixs | 21 Dec 04:55 2013
Picon

BUG #8689: createdb db finds user ? password?

The following bug has been logged on the website:

Bug reference:      8689
Logged by:          Jim Smiley
Email address:      sixs <at> ida.net
PostgreSQL version: 9.3.1
Operating system:   windows 8.1
Description:        

I installed Postgresql  9.3. I was not asked for a user. I then tried to run
created clog. I get the message

C:\Program Files\PostgreSQL\9.3\bin>createdb clog
Password:
Password:
createdb: could not connect to database template1: FATAL:  password
authenticati
on failed for user "Jim"

C:\Program Files\PostgreSQL\9.3\bin>

Is this a bug?

--

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Umesh Kirdat | 20 Dec 12:38 2013
Picon

Duplicate key violates unique constraint

Hello All,
 
I have been using pg_dump/pg_restore to backup and restore the database in our product for quite sometime.
One of our customers reported an issue with multiple entries for a field marked as primary key.
 
After investigation of the logs we found the multiple entires were created during pg_restore and the primary key constraint on the field could not be created due to unique constraint violation.
 
After searching on internet I came across the Postgres BUG 8382 & 3231 & 7760 reported in the past for this issue.
 
The Postgres version on which the issue was reported is 8.2.2
We have upgraded our Postgres version to 9.0.4 in the current product release and I would like to know whether the issue in discussion is fixed in Postgres 9.0.4?
 
Thank you,
Umesh Kirdat
peters | 20 Dec 04:09 2013
Picon

BUG #8687: HashAggregate runs out of memory

The following bug has been logged on the website:

Bug reference:      8687
Logged by:          Peter Sylvester
Email address:      peters <at> mitre.org
PostgreSQL version: 9.3.2
Operating system:   CENTOS6
Description:        

I have a query which causes out of memory conditions with a HashAggregate
plan. 

The query involves a join between a 10M row table and a 1K row table,
work_mem=300MB, process space goes over 6GB then machine runs out of memory
and swap and the OS kills the back end process.

I have a test script which I can upload to recreate the issue.

--

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Sergey Konoplev | 19 Dec 23:37 2013
Picon

Hot standby 9.2.6 -> 9.2.6 PANIC: WAL contains references to invalid pages

Hi,

Linux 2.6.32
PostgreSQL 9.2.6

It was suffering from this problem on 9.2.4, mostly last couple of
weeks when I had to rebuild the replica almost every 3 days, and I
hoped it would be fixed in 9.2.6, but it is not.

Yesterday I upgraded both master and replica servers to 9.2.6,
performed SET vacuum_freeze_table_age = 0; VACUUM; and then rebuild
the replica from scratch.

Below are replica logs, pg_controldata output, configuration and the
backtrace of the core dump. I can keep the replica untouched (without
rebuilding it) for several more hours, so please let me know if you
need me to show you something else.

Replica logs:

2013-12-19 20:51:22 MSK 19938  <at>  from  [vxid:1/0 txid:0] [] WARNING:
page 14833 of relation base/16436/3321003988 is uninitialized
2013-12-19 20:51:22 MSK 19938  <at>  from  [vxid:1/0 txid:0] [] CONTEXT:
xlog redo vacuum: rel 1663/16436/3321003988; blk 38538,
lastBlockVacuumed 0
2013-12-19 20:51:22 MSK 19938  <at>  from  [vxid:1/0 txid:0] [] PANIC:  WAL
contains references to invalid pages
2013-12-19 20:51:22 MSK 19938  <at>  from  [vxid:1/0 txid:0] [] CONTEXT:
xlog redo vacuum: rel 1663/16436/3321003988; blk 38538,
lastBlockVacuumed 0
2013-12-19 20:51:22 MSK 19935  <at>  from  [vxid: txid:0] [] LOG:  startup
process (PID 19938) was terminated by signal 6: Aborted
2013-12-19 20:51:22 MSK 19935  <at>  from  [vxid: txid:0] [] LOG:
terminating any other active server processes

pg_controldata output:

pg_control version number:            922
Catalog version number:               201204301
Database system identifier:           5858109675396804534
Database cluster state:               in archive recovery
pg_control last modified:             Thu 19 Dec 2013 08:37:31 PM MSK
Latest checkpoint location:           3271/79F16848
Prior checkpoint location:            3270/77E32598
Latest checkpoint's REDO location:    3270/929671E0
Latest checkpoint's TimeLineID:       2
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          4/1572485244
Latest checkpoint's NextOID:          3349674903
Latest checkpoint's NextMultiXactId:  6730476
Latest checkpoint's NextMultiOffset:  30866630
Latest checkpoint's oldestXID:        1340099111
Latest checkpoint's oldestXID's DB:   16436
Latest checkpoint's oldestActiveXID:  1572038146
Time of latest checkpoint:            Thu 19 Dec 2013 07:00:09 PM MSK
Minimum recovery ending location:     3272/666EE368
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:      550
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

Non default configuration:

 application_name = psql
 archive_command = (disabled)
 autovacuum_analyze_scale_factor = 0.05
 autovacuum_max_workers = 5
 autovacuum_naptime = 5
 autovacuum_vacuum_cost_delay = 5
 autovacuum_vacuum_scale_factor = 0.05
 bgwriter_delay = 10
 bgwriter_lru_multiplier = 10
 checkpoint_completion_target = 0.9
 checkpoint_segments = 1024
 checkpoint_timeout = 3600
 checkpoint_warning = 300
 client_encoding = UTF8
 DateStyle = ISO, DMY
 default_text_search_config = pg_catalog.russian
 effective_cache_size = 17432576
 effective_io_concurrency = 32
 hot_standby = on
 hot_standby_feedback = on
 lc_collate = ru_RU.UTF-8
 lc_ctype = ru_RU.UTF-8
 lc_messages = en_US.UTF-8
 lc_monetary = ru_RU.UTF-8
 lc_numeric = ru_RU.UTF-8
 lc_time = ru_RU.UTF-8
 listen_addresses = *
 log_checkpoints = on
 log_directory = /var/log/pgsql
 log_file_mode = 0600
 log_filename = postgresql-%Y-%m-%d.log
 log_line_prefix = %t %p %u <at> %d from %h [vxid:%v txid:%x] [%i]
 log_lock_waits = on
 log_rotation_size = 0
 log_statement = ddl
 log_timezone = W-SU
 log_truncate_on_rotation = on
 logging_collector = on
 maintenance_work_mem = 1048576
 max_connections = 550
 max_stack_depth = 2048
 max_standby_streaming_delay = 300000
 max_wal_senders = 3
 pg_stat_statements.track = all
 port = 6432
 server_encoding = UTF8
 shared_buffers = 4587520
 shared_preload_libraries = pg_stat_statements
 synchronous_commit = off
 TimeZone = W-SU
 track_activity_query_size = 4096
 transaction_isolation = read committed
 unix_socket_permissions = 0777
 wal_buffers = 2048
 wal_keep_segments = 2048
 wal_level = hot_standby
 work_mem = 262144

Backtrace of the coredump:

Core was generated by `postgres: startup process   recovering
000000020000327200000066 '.
Program terminated with signal 6, Aborted.
#0  0x00007f6a5a8818a5 in raise () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install
postgresql92-server-9.2.6-1PGDG.el6.x86_64
(gdb) set print pretty on
(gdb) bt f
#0  0x00007f6a5a8818a5 in raise () from /lib64/libc.so.6
No symbol table info available.
#1  0x00007f6a5a883085 in abort () from /lib64/libc.so.6
No symbol table info available.
#2  0x000000000071c580 in errfinish (dummy=<value optimized out>) at elog.c:534
        edata = 0xb88fc0
        elevel = 22
        oldcontext = 0x134a740
        econtext = 0x0
        __func__ = "errfinish"
#3  0x000000000071d09c in elog_finish (elevel=<value optimized out>,
fmt=<value optimized out>) at elog.c:1213
        edata = 0xb88fc0
        oldcontext = 0x134a740
        __func__ = "elog_finish"
#4  0x00000000004a6d6d in log_invalid_page (node=...,
forkno=MAIN_FORKNUM, blkno=14833, present=1 '\001') at xlogutils.c:91
        key = {
          node = {
            spcNode = 1663,
            dbNode = 16436,
            relNode = 3321003988
          },
          forkno = 25867822,
          blkno = 0
        }
        hentry = <value optimized out>
        found = <value optimized out>
        __func__ = "log_invalid_page"
#5  0x00000000004a6ea0 in XLogReadBufferExtended (rnode=...,
forknum=MAIN_FORKNUM, blkno=14833, mode=RBM_NORMAL) at xlogutils.c:358
        page = <value optimized out>
        lastblock = 38539
        buffer = <value optimized out>
        smgr = <value optimized out>
#6  0x00000000004897a2 in btree_xlog_vacuum (lsn=<value optimized
out>, record=0x7f6a4fb20010) at nbtxlog.c:510
        blkno = 14833
        xlrec = 0x7f6a4fb20030
        buffer = <value optimized out>
        page = <value optimized out>
        opaque = <value optimized out>
#7  btree_redo (lsn=<value optimized out>, record=0x7f6a4fb20010) at
nbtxlog.c:1062
        info = <value optimized out>
       __func__ = "btree_redo"
#8  0x00000000004a3468 in StartupXLOG () at xlog.c:6931
        recoveryContinue = 1 '\001'
        recoveryApply = <value optimized out>
        errcontext = {
          previous = 0x0,
          callback = 0x49d5a0 <rm_redo_error_callback>,
          arg = 0x7f6a4fb20010
        }
        xtime = <value optimized out>
        rmid = <value optimized out>
        xlogctl = 0x7f6a6de08480
        Insert = <value optimized out>
        checkPoint = {
          redo = {
            xlogid = 12896,
            xrecoff = 4261478784
          },
          ThisTimeLineID = 2,
          fullPageWrites = 1 '\001',
          nextXidEpoch = 4,
          nextXid = 1452297001,
          nextOid = 3344849815,
          nextMulti = 6695025,
          nextMultiOffset = 30699263,
          oldestXid = 1256929431,
          oldestXidDB = 12865,
          time = 1387392623,
          oldestActiveXid = 1452282892
        }
        wasShutdown = <value optimized out>
        reachedStopPoint = 0 '\000'
        haveBackupLabel = <value optimized out>
        RecPtr = <value optimized out>
        checkPointLoc = {
          xlogid = 12896,
          xrecoff = 4271100680
        }
        EndOfLog = <value optimized out>
        endLogId = <value optimized out>
        endLogSeg = <value optimized out>
        record = 0x7f6a4fb20010
        freespace = <value optimized out>
        oldestActiveXID = <value optimized out>
        backupEndRequired = <value optimized out>
        backupFromStandby = <value optimized out>
        dbstate_at_startup = <value optimized out>
        __func__ = "StartupXLOG"
#9  0x00000000006153b8 in StartupProcessMain () at startup.c:220
        __func__ = "StartupProcessMain"
#10 0x00000000004c3837 in AuxiliaryProcessMain (argc=2,
argv=0x7fffa02b0100) at bootstrap.c:418
        progname = 0x854a1a "postgres"
        flag = <value optimized out>
        userDoption = 0x0
        __func__ = "AuxiliaryProcessMain"
#11 0x00000000006105f3 in StartChildProcess (type=StartupProcess) at
postmaster.c:4522
        pid = <value optimized out>
        av = {0x854a1a "postgres", 0x7fffa02b0150 "-x2", 0x0,
          0x7fffa02f0814
"H\213\025e\376\377\377H+B(\213J8H#B0H\017\257\301\213J<H\323\370D;\"u\245H\003C\b1\322\061\311H\213\063H=\377ɚ;v\033\017\037\204",
          0x7fffa02b0170 "\300\226\064\001", 0x0, 0x7fffa02b0150 "-x2",
          0x7fffa02f0814
"H\213\025e\376\377\377H+B(\213J8H#B0H\017\257\301\213J<H\323\370D;\"u\245H\003C\b1\322\061\311H\213\063H=\377ɚ;v\033\017\037\204",
0xb41080 ">-\262R",
          0x0}
        ac = 2
        typebuf =
"-x2\000\377\177\000\000\274\b/\240\377\177\000\000\005\000\000\000\000\000\000\000\005\000\000\000\000\000\000"
        __func__ = "StartChildProcess"
#12 0x0000000000614c3c in PostmasterMain (argc=<value optimized out>,
argv=<value optimized out>) at postmaster.c:1123
        opt = <value optimized out>
        status = <value optimized out>
        userDoption = <value optimized out>
        listen_addr_saved = 1 '\001'
        i = <value optimized out>
        __func__ = "PostmasterMain"
#13 0x00000000005b37e0 in main (argc=5, argv=0x13496a0) at main.c:199
No locals.

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru <at> gmail.com

--

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

katsumata.tomonari | 19 Dec 03:57 2013
Picon

BUG #8686: Standby could not restart.

The following bug has been logged on the website:

Bug reference:      8686
Logged by:          Tomonari Katsumata
Email address:      katsumata.tomonari <at> po.ntts.co.jp
PostgreSQL version: 9.2.6
Operating system:   Red Hat Enterprise Linux 6.2 x86_64
Description:        

Hi,

I'm testing whether a standby could restart with asynchronous replication.

The testcase is very simple like below.
(A) start asyncronous replication
(B) stop standby with "-m f"
(C) start standby

I tried (B) and (C) periodically.

In almost cases, it worked fine.
But standby could not start one time because of PANIC.
---- Log -----
[Standby] 2013-12-09 10:42:30 JST LOG:  00000: database system was shut down
in recovery at 2013-12-09 10:42:29 JST
[Standby] 2013-12-09 10:42:30 JST LOCATION:  StartupXLOG, xlog.c:6273
cp: cannot stat `../arc/00000002.history': No such file or directory
[Standby] 2013-12-09 10:42:30 JST LOG:  00000: entering standby mode
[Standby] 2013-12-09 10:42:30 JST LOCATION:  StartupXLOG, xlog.c:6359
cp: cannot stat `../arc/000000010000000100000008': No such file or
directory
[Standby] 2013-12-09 10:42:30 JST LOG:  00000: consistent recovery state
reached at 1/8E7F110
[Standby] 2013-12-09 10:42:30 JST LOCATION:  CheckRecoveryConsistency,
xlog.c:7366
[Standby] 2013-12-09 10:42:30 JST LOG:  00000: restored log file
"000000010000000100000007" from archive
[Standby] 2013-12-09 10:42:30 JST LOCATION:  RestoreArchivedFile,
xlog.c:3273
[Standby] 2013-12-09 10:42:30 JST LOG:  00000: redo starts at 1/783B230
[Standby] 2013-12-09 10:42:30 JST LOCATION:  StartupXLOG, xlog.c:6827
[Standby] 2013-12-09 10:42:30 JST WARNING:  01000: page 1365 of relation
base/16384/16388 does not exist
[Standby] 2013-12-09 10:42:30 JST CONTEXT:  xlog redo hot_update: rel
1663/16384/16388; tid 1365/152; new 1365/153
[Standby] 2013-12-09 10:42:30 JST LOCATION:  report_invalid_page,
xlogutils.c:66
[Standby] 2013-12-09 10:42:30 JST PANIC:  XX000: WAL contains references to
invalid pages
[Standby] 2013-12-09 10:42:30 JST CONTEXT:  xlog redo hot_update: rel
1663/16384/16388; tid 1365/152; new 1365/153
[Standby] 2013-12-09 10:42:30 JST LOCATION:  log_invalid_page,
xlogutils.c:91
[Standby] 2013-12-09 10:42:30 JST LOG:  00000: startup process (PID 12560)
was terminated by signal 6: Aborted
[Standby] 2013-12-09 10:42:30 JST LOCATION:  LogChildExit,
postmaster.c:2895
[Standby] 2013-12-09 10:42:30 JST LOG:  00000: terminating any other active
server processes
[Standby] 2013-12-09 10:42:30 JST LOCATION:  HandleChildCrash,
postmaster.c:2682
[Standby] 2013-12-09 11:10:12 JST LOG:  00000: database system was
interrupted while in recovery at log time 2013-12-09 10:32:14 JST
[Standby] 2013-12-09 11:10:12 JST HINT:  If this has occurred more than once
some data might be corrupted and you might need to choose an earlier
recovery target.
[Standby] 2013-12-09 11:10:12 JST LOCATION:  StartupXLOG, xlog.c:6289
---------

I tried to fix this problem.
At first, I doubted the recovery state reached "consistent" before redo
starts.
And then I checked pg_control and related WAL.
The WAL sequence is like below.

WAL--(a)--(b)--(c)--(d)--(e)-->
================================================
(a) Latest checkpoint's REDO location
1/783B230

(b) hot_update
1/7842010

(c) truncate
1/8E7E5C8

(d) Latest checkpoint location
1/8E7F0B0

(e) Minimum recovery ending location
1/8E7F110
================================================

>From these things, I found it has happened with this scenario.
----------
(1) standby starting
(2) seeking checkpoint location 1/8E7F0B0 because backup_label is not
absecnt
(3) reachedConsistency is set to true at 1/8E7F110 in
CheckRecoveryConsistent
(4) redo start from 1/783B230
(5) PANIC at 1/7842010 because reachedConsistency has set already and
operating against a block which will be truncated at (c).
----------

At step(2), EndRecPtr is set to 1/8E7F110(next to 1/8E7F0B0),
so reachedConsistency is set to true at step(3).

I think it's not need to increase EndRecPtr while seeking checkpoint
location.
I tried to revise it and this worked fine.

I think this is very very narrow gate, but it could happen.

I'm not sure this problem happen with synchronous replication or
another version of PostgreSQL(ie. 9.3/9.1/9.0), 
but at least we need to fix it.

regards,
----------------
Tomonari Katsumata

--

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

mz | 18 Dec 20:08 2013
Picon
Picon

BUG #8685: "alter default privileges" cannot revoke default execute privilege on functions

The following bug has been logged on the website:

Bug reference:      8685
Logged by:          Manuel Zahariev
Email address:      mz <at> alumni.sfu.ca
PostgreSQL version: 9.1.11
Operating system:   Ubuntu 13.10
Description:        

ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM <role>
...does not remove default execute privileges on functions

====================
Log:

$sudo -u postgres psql
psql (9.1.11)
Type "help" for help.

postgres=# CREATE DATABASE db;
CREATE DATABASE
postgres=# CREATE ROLE u WITH PASSWORD '1234' login;
CREATE ROLE
postgres=# \c db
You are now connected to database "db" as user "postgres".
db=# ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM u;
ALTER DEFAULT PRIVILEGES
db=# CREATE FUNCTION f() RETURNS varchar AS $$
db$# SELECT 'Hello'::varchar;
db$# $$ LANGUAGE 'SQL' SECURITY DEFINER;
CREATE FUNCTION
db=# SELECT * FROM f();
   f   
-------
 Hello
(1 row)

db=# \q
$psql -h localhost db u
Password for user u: 
psql (9.1.11)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

db=> SELECT * FROM f();  -- should fail
   f   
-------
 Hello
(1 row)

--

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

bneumeier | 17 Dec 18:10 2013
Picon

BUG #8684: Tables with custom range domain type cannot be analyzed

The following bug has been logged on the website:

Bug reference:      8684
Logged by:          Brett Neumeier
Email address:      bneumeier <at> gmail.com
PostgreSQL version: 9.3.2
Operating system:   Ubuntu Linux 13.04 64-bit
Description:        

In 9.3.2 and master HEAD (3b97e6823b), in a freshly-created postgresql
database (with all tests passing from "make check-world"), this sequence:

-----cut here-----
CREATE DOMAIN range_domain AS tstzrange
	CONSTRAINT exclusive_upper CHECK ((NOT upper_inc(VALUE)))
	CONSTRAINT inclusive_lower CHECK (lower_inc(VALUE));

CREATE TABLE cannot_analyze (
    rng range_domain NOT NULL
);

VACUUM ANALYZE;
-----cut here-----

results in the analyze failing with "ERROR:  type 16385 is not a range type"
rather than succeeding.

(This error is being emitted from range_get_typcache because the result of
lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO) has a rngelemtype of NULL,
but I haven't tried to figure out why that is or what it should be instead.)

--

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

gert.wieberdink | 15 Dec 21:51 2013
Picon

BUG #8683: pg_upgrade

The following bug has been logged on the website:

Bug reference:      8683
Logged by:          gertw
Email address:      gert.wieberdink <at> ziggo.nl
PostgreSQL version: 9.3.2
Operating system:   CentOS 6.4 x86_64
Description:        

pg_upgrade from 9.2.6 to 9.3.2 does not work. Error msg is: ERROR:  could
not access file "$libdir/postgis-2.0": No such file or directory.

No matter whether I remove all postgis2_92 stuff or ADD postgis2_93 stuff.
Error remains and I cannot upgrade. Please advise.

Kind regards,
-gertw

--

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

arjsia | 14 Dec 07:59 2013
Picon

BUG #8682: sqlstate = 28000

The following bug has been logged on the website:

Bug reference:      8682
Logged by:          abhay jadhav
Email address:      arjsia <at> gmail.com
PostgreSQL version: 8.4.0
Operating system:   win7 and xp
Description:        

sql state = 28000 error. when the application starts. please reply with
solution

--

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

david fleischhauer | 13 Dec 18:30 2013
Picon

permission issues with PostgreSQL 9.2 EnterpriseDB one-click installer on windows 7 causes initcluster to fail

I have noted two bugs dealing with permissions with the EnterpriseDB one-click installer.  Both are similar cases:


1.  Permissions are not given to the PostgreSQL bin directory.  If I try to install postgres on a drive with limited permissions (for my test, only the 'Administrators' group had permissions), I get an error saying "libintl-8.dll" is missing.  That file is located in the postgres bin directory.  The issue is that your initcluster.vbs script only gives permissions for the data directory and the parent directories of the data directory.  In order for postgres to install correctly, permissions need to be added for the bin directory.

2.  Permissions are not properly given to the PostgreSQL data directory's root drive in PostgreSQL version 9.2.5 and up.  In PostgreSQL 9.2.4 there is a comment in the initcluster.vbs script saying:

    ' Drive letter must not be surrounded by double-quotes and ends with slash (\)
    ' "icacls" fails on the drives with (NP) flag

In version 9.2.5, the initcluster.vbs script has been changed and the above corner case is not taken care of.  Again, to reproduce this issue, I set the E drive of my machine to only give permissions to the 'Administrators' group and my E drive was completely empty.  I also had to fixed issue #1 to get this issue to pop up.  The error I am getting from the logfile is:

    The database cluster will be initialized with locale "English_United States.1252".
    The default text search configuration will be set to "english".

    fixing permissions on existing directory E:/dir/data ... ok
    creating subdirectories ... initdb: could not create directory "E:/dir": File exists
    initdb: removing contents of data directory "E:/dir/data"

    Called Die(Failed to initialise the database cluster with initdb)...
    Failed to initialise the database cluster with initdb

Here are the slight differences between the icacls command to grant permissions to the root drive in 9.2.4 and 9.2.5:

    9.2.4:    icacls E:\ /grant ...
    9.2.5:    icacls "E:" /grant ...

As your comment shows, having quotes around 'E:' and also not including the slash will cause an issue, both of which are not taken care of in the 9.2.5 icacls command.

Hopefully I have clearly stated the issues.  If these issues have not been reported and there are any issues understanding what I wrote, feel free to reply to this email.

thanks,
David

Gmane