Re: [GENERAL] count case when - PG 9.2

[Yasin Sari]

if you want see account_status and the count()- try this:

SELECT

CASE

WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14 day'))

THEN 'trial'

WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37 day'))

THEN 'paying'

END as account_status,

c ount(*)

FROM public.clients c

WHERE (

   (last_pay > EXTRACT('epoch' FROM now() - '12
Months'::INTERVAL))

  )

   group by 1

ORDER BY 1


10 Mar 2017 Cum, 00:02 tarihinde, Patrick B  şunu
yazdı:

> 2017-03-09 23:15 GMT+13:00 vinny :
>
> On 2017-03-09 05:27, Patrick B wrote:
>
> Hi guys. How can I count using 'CASE WHEN'?
>
> Example:
>
> SELECT
>
>
> CASE
>
>
> WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL
> '14 day'))
>
>
> THEN 'trial'
>
>
> WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37
> day'))
>
>
> THEN 'paying'
>
>
> END as account_status,
>
>
> c.id [1]
>
>
> FROM public.clients c
>
>
> WHERE (
>
>
> (last_pay > EXTRACT('epoch' FROM now() - '12
> Months'::INTERVAL))
>
>
> )
>
>
> ORDER BY 1
>
>  I wanna know how many of 'trial' and 'paying' customers the query
> returns. can you guys please advice how to do it?
>
> Thanks
> Patrick
>
>
> comparisons like "A>B" return a boolean. Booleans can be cast to integers,
> and integers can be summed.
>
> SUM((A>B)::int)
>
> But depending on the situation, indexes etc it could be faster to run e
> separate count query, you'll have to test that.
>
>
>
> Could you please guys give me a query as an example?
>
> Thanks
> P.
>

Re: [GENERAL] Recovery damaged dump file

[mac pack]

2017-03-10 5:11 GMT+00:00 Adrian Klaver :

> On 03/09/2017 09:09 PM, Adrian Klaver wrote:
>
>> On 03/09/2017 03:55 AM, mac pack wrote:
>>
>>> Hi.
>>>
>>> My PostgreSQL server was affect by a Ransomware virus. I'm trying to
>>> restore the database from a dump file made by pg_dump in custom format
>>> (-F c option), but the dump file seems to be damaged in the first's 1000
>>> lines.
>>>
>>> Opening the file with vi shows ^@^@^@^@^@^@^@^@^@^@^ followed by part of
>>> the databse schema and the a lote of lines with binary characters that i
>>> think is the table's data.
>>>
>>
>> The custom format is a binary format so non text characters would be
>> expected.
>>
>> Did you try to restore using the file, before doing the below?
>>
>>
yes


> If there was an error when you did that and if so what was it?
>>
>
> Should be:
>
> Was there an error when you did that and if so what was it?
>
>
pg_restore db.bckup > out.sql
pg_restore: [archiver] input file does not appear to be a valid archive

Opening the original damaged file in vi with :%!xxd it show's:

0003ff80:          
0003ff90:          
0003ffa0:          
0003ffb0:          
0003ffc0:          
0003ffd0:          
0003ffe0:          
0003fff0:          
0004: 3331 3820 2020 2020 2020 2020 2020 202d  318-
00040010: 272c 206e 756c 6c29 3b0d 0a69 6e73 6572  ', null);..inser
00040020: 7420 696e 746f 206d 6f72 6164 6173 6374  t into moradasct
00040030: 7428 6964 5f64 6973 7463 6f6e 632c 206c  t(id_distconc, l
00040040: 6f63 616c 6964 6164 652c 2061 7272 7561  ocalidade, arrua
00040050: 6d65 6e74 6f2c 2074 726f 636f 2c20 6c69  mento, troco, li
00040060: 6d5f 696e 6665 7269 6f72 5f70 6f72 7461  m_inferior_porta
00040070: 2c20 636c 6965 6e74 652c 2063 6f64 6967  , cliente, codig
00040080: 6f5f 706f 7374 616c 2c20 6c69 6d5f 7375  o_postal, lim_su
00040090: 7065 7269 6f72 2920 7661 6c75 6573 2028  perior) values (




>
>>
>>> I tried to replace those first lines with lines from other dev database
>>> and i can run pg_restore but at some point throws error.
>>>
>>> pg_restore  db.backup > out.sql
>>>
>>> pg_restore: [custom archiver] unrecognized data block type (0) while
>>> searching archive
>>>
>>> The follow commands works fine:
>>> pg_restore  -s db.backup > out.sql
>>> pg_restore -l db.backup
>>>
>>> Do you think it's possible to recover the dump file, is there any method
>>> or tool to recover dump files?
>>>
>>>
>>> Thanks.
>>> Mário
>>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Unable to start postgresql

[Michael Paquier]

On Sat, Mar 11, 2017 at 12:15 AM, Adrian Klaver
 wrote:
> Two resources that help me keep within shouting distance of the latest and
> greatest:
>
> Hubert 'depesz' Lubaczewski
> Waiting for PostgreSQL * series:
>
> https://www.depesz.com/
> In the search box type 'Waiting for'
>
> The pgsql-announce list:
>
> http://www.postgresql.org/mailpref/pgsql-announce
>
> In particular the PostgreSQL Weekly News that comes out Sunday or Monday
> from David Fetter.

Ans also Planet Postgres is a good resource on the matter, it gathers
posts of many blogs from hackers and users:
https://planet.postgresql.org/
-- 
Michael


-- 
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] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[Rich Shepard]

On Fri, 10 Mar 2017, Adrian Klaver wrote:


That this looks like a merge of the sample file:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/libpq/pg_hba.conf.sample;h=6b1778a72136edf52cea56f2ab088b9449df9a48;hb=HEAD
with your additions.



Seems you have not restarted Postgres since the last time you had a valid
pg_hba.conf file. The above is not one. Remove the @***@ strings and you
should be good.


Adrian,

  I accessed a postgres database (my bookkeeping data) yesterday and the
only difference from then to now was upgrading postgres one third-digit
version.

  Prior to today my upgrades were generally one or two minor versions; e.g.,
from 9.3.x to 9.5.x, and done with pg_dumpall and restoration using psql.
Today I just used pg_ctl to stop the server, ran the normal Slackware
upgradepkg routines, and tried to restart the server. I do not see a
pg_hba.conf file anywhere in the source or build trees so I've no idea from
where this unworkable version came.

Thanks,

Rich


--
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] Upgradede -9.6.1 to -9.6.2; postmaster will not start [FIXED]

[Rich Shepard]

On Fri, 10 Mar 2017, Tom Lane wrote:


The "@remove-line-for-nolocal@" bits should not be there. initdb would
normally either delete those lines entirely, or strip off
"@remove-line-for-nolocal@", depending on the switches it was given.
Likewise the various other @something@ bits should have been changed to
something else.

It certainly wasn't working before if it was like this, either.


Tom,

  I've no idea how the file was changed, but something certainly happened to
it. Restoring pg_hba.conf from 2016-09-17 (when pg-9.5.1 was installed)
certainly fixed the issue.

Many thanks,

Rich


--
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] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[Adrian Klaver]

On 03/10/2017 11:50 AM, Rich Shepard wrote:

On Fri, 10 Mar 2017, Rich Shepard wrote:


Actually, it has the modifications I've made over the years. That's why I
could not see what changed.


  Well, postgres was running yesterday and allowed me to access my
bookkeeping software so obviously something did change without my manually
editing pg_hba.conf. Here are the log records:

LOG:  could not open secondary authentication file "@authcomment@" as
"/var/lib/pgsql/9.6/data/authcomment@": No such file or directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@local" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@local": No such file or
directory
LOG:  could not open secondary authentication file "@authmethodlocal@" as
"/var/lib/pgsql/9.6/data/authmethodlocal@": No such file or directory
LOG:  could not open secondary authentication file "@authmethodhost@" as
"/var/lib/pgsql/9.6/data/authmethodhost@": No such file or directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  invalid connection type "all"
CONTEXT:  line 80 of configuration file
"/var/lib/pgsql/9.6/data/pg_hba.conf"
LOG:  end-of-line before authentication method
CONTEXT:  line 86 of configuration file
"/var/lib/pgsql/9.6/data/pg_hba.conf"
FATAL:  could not load pg_hba.conf
LOG:  database system is shut down

  Postgres runs on my desktop server/workstation and I'm the only user
loggin in. Here's pg_hba.conf (restored from 2-22-2017 backup) which still
does not work. I've read the pg_hba.conf section in the 9.6 docs without
seeing what's wrong with my file.

# PostgreSQL Client Authentication Configuration File
# ===
# Put your actual configuration here
# --
@authcomment@

# TYPE  DATABASEUSERADDRESS METHOD

@remove-line-for-nolocal@# "local" is for Unix domain socket connections
only
@remove-line-for-nolocal@local   all
all @authmethodlocal@
# IPv4 local connections:
localallrshepardtrust
hostall rshepard127.0.0.1/32trust
hostnossl allrshepard127.0.0.1/32trust
# IPv6 local connections:
hostall all ::1/128
@authmethodhost@
# Allow replication connections from localhost, by a user with the
# replication privilege.
@remove-line-for-nolocal@#local   replication
@default_username@@authmethodlocal@
#hostreplication @default_username@
127.0.0.1/32@authmethodhost@
#hostreplication @default_username@
::1/128 @authmethodhost@

  What am I not seeing?


That this looks like a merge of the sample file:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/libpq/pg_hba.conf.sample;h=6b1778a72136edf52cea56f2ab088b9449df9a48;hb=HEAD

with your additions.

Seems you have not restarted Postgres since the last time you had a 
valid pg_hba.conf file. The above is not one. Remove the @***@ strings 
and you should be good.





Rich





--
Adrian Klaver
adrian.kla...@aklaver.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] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[Tom Lane]

Rich Shepard  writes:
>Postgres runs on my desktop server/workstation and I'm the only user
> loggin in. Here's pg_hba.conf (restored from 2-22-2017 backup) which still
> does not work. I've read the pg_hba.conf section in the 9.6 docs without
> seeing what's wrong with my file.

> # PostgreSQL Client Authentication Configuration File
> # ===
> # Put your actual configuration here
> # --
> @authcomment@

> # TYPE  DATABASEUSERADDRESS METHOD

> @remove-line-for-nolocal@# "local" is for Unix domain socket connections only
> @remove-line-for-nolocal@local   all all  
>@authmethodlocal@
> # IPv4 local connections:
> local all rshepardtrust
> hostall rshepard127.0.0.1/32trust
> hostnossl all rshepard127.0.0.1/32trust
> # IPv6 local connections:
> hostall all ::1/128 
> @authmethodhost@
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> @remove-line-for-nolocal@#local   replication @default_username@  
>   @authmethodlocal@
> #hostreplication @default_username@127.0.0.1/32
> @authmethodhost@
> #hostreplication @default_username@::1/128 
> @authmethodhost@

>What am I not seeing?

The "@remove-line-for-nolocal@" bits should not be there.
initdb would normally either delete those lines entirely, or
strip off "@remove-line-for-nolocal@", depending on the switches
it was given.  Likewise the various other @something@ bits should
have been changed to something else.

It certainly wasn't working before if it was like this, either.

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] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[Rich Shepard]

On Fri, 10 Mar 2017, Rich Shepard wrote:


Actually, it has the modifications I've made over the years. That's why I
could not see what changed.


  Well, postgres was running yesterday and allowed me to access my
bookkeeping software so obviously something did change without my manually
editing pg_hba.conf. Here are the log records:

LOG:  could not open secondary authentication file "@authcomment@" as
"/var/lib/pgsql/9.6/data/authcomment@": No such file or directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@local" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@local": No such file or
directory
LOG:  could not open secondary authentication file "@authmethodlocal@" as
"/var/lib/pgsql/9.6/data/authmethodlocal@": No such file or directory
LOG:  could not open secondary authentication file "@authmethodhost@" as
"/var/lib/pgsql/9.6/data/authmethodhost@": No such file or directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  invalid connection type "all"
CONTEXT:  line 80 of configuration file
"/var/lib/pgsql/9.6/data/pg_hba.conf"
LOG:  end-of-line before authentication method
CONTEXT:  line 86 of configuration file
"/var/lib/pgsql/9.6/data/pg_hba.conf"
FATAL:  could not load pg_hba.conf
LOG:  database system is shut down

  Postgres runs on my desktop server/workstation and I'm the only user
loggin in. Here's pg_hba.conf (restored from 2-22-2017 backup) which still
does not work. I've read the pg_hba.conf section in the 9.6 docs without
seeing what's wrong with my file.

# PostgreSQL Client Authentication Configuration File
# ===
# Put your actual configuration here
# --
@authcomment@

# TYPE  DATABASEUSERADDRESS METHOD

@remove-line-for-nolocal@# "local" is for Unix domain socket connections only
@remove-line-for-nolocal@local   all all
 @authmethodlocal@
# IPv4 local connections:
local   all rshepardtrust
hostall rshepard127.0.0.1/32trust
hostnossl all   rshepard127.0.0.1/32trust
# IPv6 local connections:
hostall all ::1/128 @authmethodhost@
# Allow replication connections from localhost, by a user with the
# replication privilege.
@remove-line-for-nolocal@#local   replication @default_username@
@authmethodlocal@
#hostreplication @default_username@127.0.0.1/32
@authmethodhost@
#hostreplication @default_username@::1/128 
@authmethodhost@

  What am I not seeing?

Rich


--
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] INSERT and ON CONFLICT

[Rich Shepard]

On Fri, 10 Mar 2017, Brian Dunavant wrote:


I believe the following test should answer your question.


  Thank you, Brian. It does answer my question.

Regards,

Rich


--
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] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[Rich Shepard]

On Fri, 10 Mar 2017, Tom Lane wrote:


It looks like what you have in pg_hba.conf is a raw copy of
pg_hba.conf.sample, without any of the editing that initdb normally
applies to it (to say nothing of manual adjustments you might make later).


Tom,

  Actually, it has the modifications I've made over the years. That's why I
could not see what changed.


It's hard to tell from the information given whether this is pilot error
or something broken in SlackBuilds' upgrade script.


  I've not modified the file in a while, and don't overwrite the existing
one when a new one is available. This is a new error for me.


In any case, you should be able to fix it by making a copy of your
pre-upgrade pg_hba.conf and shoving that back into the data directory
afterwards;


  I'll restore a backup copy from last month.

Thanks,

Rich



--
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] INSERT and ON CONFLICT

[Brian Dunavant]

I believe the following test should answer your question.

db=# create table test ( a integer not null unique );
CREATE TABLE

db=# insert into test values (1);
INSERT 0 1

db=# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_a_key"
DETAIL:  Key (a)=(1) already exists.

db=# insert into test values (1) on conflict do nothing;
INSERT 0 0



On Fri, Mar 10, 2017 at 12:35 PM, Rich Shepard  wrote:
>   I'm filling a table with rows and have the first batch successfully
> inserted. When I add more rows there may be some that already exist in the
> table and I would prefer that they be ignored and the insert process
> continue.
>
>   The syntax page for INSERT suggests that ON CONFLICT DO NOTHING is exactly
> what I want to include in the command. Have I correctly interpreted what the
> DO NOTHING option does when a row to be inserted already is present in the
> table?
>
> Rich
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[Tom Lane]

Rich Shepard  writes:
> postgres@salmo:~$ postgres -D /var/lib/pgsql/9.6/data/ &
> [1] 17585
> postgres@salmo:~$ LOG:  could not open secondary authentication file
> "@authcomment@" as "/var/lib/pgsql/9.6/data/authcomment@": No such file or
> directory
> LOG:  could not open secondary authentication file
> "@remove-line-for-nolocal@" as
> "/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
> directory
> LOG:  could not open secondary authentication file
> "@remove-line-for-nolocal@local" as
> "/var/lib/pgsql/9.6/data/remove-line-for-nolocal@local": No such file or
> directory

It looks like what you have in pg_hba.conf is a raw copy of
pg_hba.conf.sample, without any of the editing that initdb normally
applies to it (to say nothing of manual adjustments you might make
later).  It's hard to tell from the information given whether this
is pilot error or something broken in SlackBuilds' upgrade script.

In any case, you should be able to fix it by making a copy of your
pre-upgrade pg_hba.conf and shoving that back into the data directory
afterwards; there's no reason to change that during a minor-version
upgrade.  I'd check the other configuration files in $PGDATA too,
just in case SlackBuilds is fat-fingering them as well.

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] INSERT and ON CONFLICT

[Rich Shepard]

  I'm filling a table with rows and have the first batch successfully
inserted. When I add more rows there may be some that already exist in the
table and I would prefer that they be ignored and the insert process
continue.

  The syntax page for INSERT suggests that ON CONFLICT DO NOTHING is exactly
what I want to include in the command. Have I correctly interpreted what the
DO NOTHING option does when a row to be inserted already is present in the
table?

Rich


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

[GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[Rich Shepard]

  I've tried resolving this but do not see what changed.

  1. Shut down postgres:
pg_ctl stop -D /var/lib/pgsql/9.6/data

  2. Upgraded version (using SlackBuilds.org package as usual).

  3. Tried re-starting postgres:

postgres@salmo:~$ postgres -D /var/lib/pgsql/9.6/data/ &
[1] 17585
postgres@salmo:~$ LOG:  could not open secondary authentication file
"@authcomment@" as "/var/lib/pgsql/9.6/data/authcomment@": No such file or
directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@local" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@local": No such file or
directory
LOG:  could not open secondary authentication file "@authmethodlocal@" as
"/var/lib/pgsql/9.6/data/authmethodlocal@": No such file or directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  invalid connection type "all"
CONTEXT:  line 80 of configuration file
"/var/lib/pgsql/9.6/data/pg_hba.conf"
FATAL:  could not load pg_hba.conf
LOG:  database system is shut down

  4. Line 80 in pg_hba.conf:
@remove-line-for-nolocal@local   allall   @authmethodlocal@

  5. Tried commenting out that line to match backup, but that also did not
allow postgres to start.

  6. All files are owned by postgres.users.

  Please teach me how to fix this.

TIA,

Rich



--
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 start postgresql

[Adrian Klaver]

On 03/10/2017 07:08 AM, John Iliffe wrote:

On Friday 10 March 2017 09:25:25 Melvin Davidson wrote:



*Probably "PostgreSQL Administration Essentials" would be good to start
with.*

Thank you.  I was unaware of this source.

I have a similar book but it is about 8 years old so probably out of date.


Two resources that help me keep within shouting distance of the latest 
and greatest:


Hubert 'depesz' Lubaczewski
Waiting for PostgreSQL * series:

https://www.depesz.com/
In the search box type 'Waiting for'

The pgsql-announce list:

http://www.postgresql.org/mailpref/pgsql-announce

In particular the PostgreSQL Weekly News that comes out Sunday or Monday 
from David Fetter.




John




--
Adrian Klaver
adrian.kla...@aklaver.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] Unable to start postgresql

[John Iliffe]

On Friday 10 March 2017 09:25:25 Melvin Davidson wrote:
> On Thu, Mar 9, 2017 at 10:01 PM, John Iliffe  wrote:
> > On Thursday 09 March 2017 16:37:28 you wrote:
> > > On 03/08/2017 10:12 AM, John Iliffe wrote:
> > > > I think you may have hit it but I'm even more confused now.
> > > > 
> > > > I looked at the running Postgesql on the current server and there
> > > > is a 5th sub-directory called /data.  That is on the same level
> > > > as the /bin, /share, etc.  In this new installation it is not
> > > > present and neither is the postgresql.conf file, nor are the hba
> > > > files that restrict logins.
> > > > 
> > > > So, my question:  is this a change between version 9.2.1 and 9.6.2
> > > > and if so where is the postgresql.conf file (I can't find it on a
> > > > scan but it could be renamed I suppose)?
> > > > 
> > > > Or, is there something wrong with the installation?  I went by the
> > > > successful conclusion message from make install and assumed
> > > > everything would be as expected in the directories.
> > > > 
> > > > Any ideas as to what may have happened?
> > > 
> > > Just realized this was an old post that just came through. I also
> > > realized that my explanation of where the *.conf files are was
> > > specific to a source default install. Package installs may locate
> > > them elsewhere. In that case as a Postgres superuser(postgres for
> > > example) do:
> > > 
> > > test=# select * from pg_settings where sourcefile is not null;
> > > 
> > >   .
> > > 
> > > name| DateStyle
> > > setting | ISO, MDY
> > > unit| NULL
> > > category| Client Connection Defaults / Locale and Formatting
> > > short_desc  | Sets the display format for date and time values.
> > > extra_desc  | Also controls interpretation of ambiguous date
> > > inputs. context | user
> > > vartype | string
> > > source  | configuration file
> > > min_val | NULL
> > > max_val | NULL
> > > enumvals| NULL
> > > boot_val| ISO, MDY
> > > reset_val   | ISO, MDY
> > > sourcefile  | /usr/local/pgsql/data/postgresql.conf
> > > sourceline  | 538
> > > pending_restart | f
> > > 
> > >   .
> > > 
> > > And look for the sourcefile location.
> > > 
> > > > John
> > 
> > Thanks Adrian.
> > 
> > I had figured that out eventually, and found the missing files where
> > you said
> > they were.
> > 
> > Appreciate the info on how to get the configuration values.  A lot
> > seems to have changed since I started using Postgresql and I guess I
> > should really read up on how things work now and not in 2008!
> > 
> > Regards,
> > 
> > John
> > 
> > 
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> 
> *John,*
> 
> 
> 
> 
> *If you are really serious about reading up, I suggest you pick from the
> books on this
> url:https://www.packtpub.com/all-books?search==84==
> *
> 
> *Probably "PostgreSQL Administration Essentials" would be good to start
> with.*
Thank you.  I was unaware of this source.

I have a similar book but it is about 8 years old so probably out of date.

John


-- 
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] Recovery damaged dump file

[Adrian Klaver]

On 03/10/2017 01:25 AM, mac pack wrote:


2017-03-10 5:11 GMT+00:00 Adrian Klaver >:

On 03/09/2017 09:09 PM, Adrian Klaver wrote:

On 03/09/2017 03:55 AM, mac pack wrote:

Hi.

My PostgreSQL server was affect by a Ransomware virus. I'm
trying to
restore the database from a dump file made by pg_dump in
custom format
(-F c option), but the dump file seems to be damaged in the
first's 1000
lines.

Opening the file with vi shows ^@^@^@^@^@^@^@^@^@^@^
followed by part of
the databse schema and the a lote of lines with binary
characters that i
think is the table's data.


The custom format is a binary format so non text characters would be
expected.

Did you try to restore using the file, before doing the below?


yes


If there was an error when you did that and if so what was it?


Should be:

Was there an error when you did that and if so what was it?


pg_restore db.bckup > out.sql
pg_restore: [archiver] input file does not appear to be a valid archive

Opening the original damaged file in vi with :%!xxd it show's:


So something zeroed out the beginning of the file.

I know you said:

pg_restore  -s db.backup > out.sql

works. Does the opposite work also?:

pg_restore  -a db.backup > out.sql

I would say start with Michael's suggestion of working through object by 
object.


You mentioned another dev database, how close is that to the content of 
the damaged database?





0003ff80:          
0003ff90:          
0003ffa0:          
0003ffb0:          
0003ffc0:          
0003ffd0:          
0003ffe0:          
0003fff0:          
0004: 3331 3820 2020 2020 2020 2020 2020 202d  318-
00040010: 272c 206e 756c 6c29 3b0d 0a69 6e73 6572  ', null);..inser
00040020: 7420 696e 746f 206d 6f72 6164 6173 6374  t into moradasct
00040030: 7428 6964 5f64 6973 7463 6f6e 632c 206c  t(id_distconc, l
00040040: 6f63 616c 6964 6164 652c 2061 7272 7561  ocalidade, arrua
00040050: 6d65 6e74 6f2c 2074 726f 636f 2c20 6c69  mento, troco, li
00040060: 6d5f 696e 6665 7269 6f72 5f70 6f72 7461  m_inferior_porta
00040070: 2c20 636c 6965 6e74 652c 2063 6f64 6967  , cliente, codig
00040080: 6f5f 706f 7374 616c 2c20 6c69 6d5f 7375  o_postal, lim_su
00040090: 7065 7269 6f72 2920 7661 6c75 6573 2028  perior) values (






I tried to replace those first lines with lines from other
dev database
and i can run pg_restore but at some point throws error.

pg_restore  db.backup > out.sql

pg_restore: [custom archiver] unrecognized data block type
(0) while
searching archive

The follow commands works fine:
pg_restore  -s db.backup > out.sql
pg_restore -l db.backup

Do you think it's possible to recover the dump file, is
there any method
or tool to recover dump files?


Thanks.
Mário





--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.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] Unable to start postgresql

[Melvin Davidson]

On Thu, Mar 9, 2017 at 10:01 PM, John Iliffe  wrote:

> On Thursday 09 March 2017 16:37:28 you wrote:
> > On 03/08/2017 10:12 AM, John Iliffe wrote:
> > > I think you may have hit it but I'm even more confused now.
> > >
> > > I looked at the running Postgesql on the current server and there is a
> > > 5th sub-directory called /data.  That is on the same level as the
> > > /bin, /share, etc.  In this new installation it is not present and
> > > neither is the postgresql.conf file, nor are the hba files that
> > > restrict logins.
> > >
> > > So, my question:  is this a change between version 9.2.1 and 9.6.2 and
> > > if so where is the postgresql.conf file (I can't find it on a scan
> > > but it could be renamed I suppose)?
> > >
> > > Or, is there something wrong with the installation?  I went by the
> > > successful conclusion message from make install and assumed everything
> > > would be as expected in the directories.
> > >
> > > Any ideas as to what may have happened?
> >
> > Just realized this was an old post that just came through. I also
> > realized that my explanation of where the *.conf files are was specific
> > to a source default install. Package installs may locate them elsewhere.
> > In that case as a Postgres superuser(postgres for example) do:
> >
> > test=# select * from pg_settings where sourcefile is not null;
> >   .
> >
> > name| DateStyle
> > setting | ISO, MDY
> > unit| NULL
> > category| Client Connection Defaults / Locale and Formatting
> > short_desc  | Sets the display format for date and time values.
> > extra_desc  | Also controls interpretation of ambiguous date inputs.
> > context | user
> > vartype | string
> > source  | configuration file
> > min_val | NULL
> > max_val | NULL
> > enumvals| NULL
> > boot_val| ISO, MDY
> > reset_val   | ISO, MDY
> > sourcefile  | /usr/local/pgsql/data/postgresql.conf
> > sourceline  | 538
> > pending_restart | f
> >   .
> >
> > And look for the sourcefile location.
> >
> > > John
>
>
> Thanks Adrian.
>
> I had figured that out eventually, and found the missing files where you
> said
> they were.
>
> Appreciate the info on how to get the configuration values.  A lot seems to
> have changed since I started using Postgresql and I guess I should really
> read up on how things work now and not in 2008!
>
> Regards,
>
> John
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



*John,*




*If you are really serious about reading up, I suggest you pick from the
books on this
url:https://www.packtpub.com/all-books?search==84==
*

*Probably "PostgreSQL Administration Essentials" would be good to start
with.*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Running TAP regression tests under windows/msvc

[Michael Paquier]

On Wed, Mar 8, 2017 at 6:30 AM, Mark Dilger  wrote:
>
>> On Mar 7, 2017, at 12:24 PM, Mark Dilger  wrote:
>>
>> Hello,
>>
>> I am attempting to get the tap tests working under windows so as to
>> help review patches for the 10.0 development cycle.  I can compile
>> the sources on windows 2008 using the MS Visual C and run the
>>
>>   vcregress.bat check
>>
>> without any problems or failures.  In an attempt to run the tap tests,
>> I have edited config_default.pl as follows:
>>
>> diff --git a/src/tools/msvc/config_default.pl 
>> b/src/tools/msvc/config_default.pl
>> index 97f1af8..1e7b19d 100644
>> --- a/src/tools/msvc/config_default.pl
>> +++ b/src/tools/msvc/config_default.pl
>> @@ -16,7 +16,7 @@ our $config = {
>>extraver  => undef,# --with-extra-version=
>>gss   => undef,# --with-gssapi=
>>nls   => undef,# --enable-nls=
>> -   tap_tests => undef,# --enable-tap-tests
>> +   tap_tests => 1,# --enable-tap-tests
>>tcl   => undef,# --with-tls=
>>perl  => undef,# --with-perl
>>python=> undef,# --with-python=
>>
>> and when I run
>>
>>   vcregress.bat bincheck
>>
>> I get a few failures.  Am I doing something wrong, or are these failures
>> the same for other folks?  A portion of the log of the regressions follows:

I don't think you are doing anything wrong here.

> I added a bit of debugging logic to PostgresNode.pm, to print out the
> name of the log file being grep'd and the size of that file, and it seems
> in these cases the log file is of size zero (empty).
>
> not ok 10 - SQL CLUSTER run: SQL found in server log 
> c:/jenkins/workspace/unicorns/postgresql/src/bin/scripts/tmp_check/log/010_clusterdb_main.log,
>  length 0
> ok 11 - fails with nonexistent table
> ok 12 - clusterdb -t test1 exit code 0
> not ok 13 - cluster specific table: SQL found in server log 
> c:/jenkins/workspace/unicorns/postgresql/src/bin/scripts/tmp_check/log/010_clusterdb_main.log,
>  length 0
> ok 14 - clusterdb with connection string
> Dubious, test returned 2 (wstat 512, 0x200)
> Failed 2/14 subtests
>
> Perhaps there is a race condition between when the test is run and when the
> log file is flushed?  I'm just guessing here

Just running now the tests by myself to see if I could reproduce your
failures, I have bumped before that into failures for the tests of
pg_ctl and the tests of pg_rewind not responding.. This needs some
serious investigation.

The buildfarm is not running those tests by the way, that's annoying.
-- 
Michael


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