Can a broken Postgresql data directory be reconstructed without losing data?

[A A]

I'm trying to restore a database from a broken data directory that have lost 
many of its files accidentally.

I have tried unsuccessfully to install new instance of the same Postgresql 
version and then copy (with overwrite) the broken datadir to the new one.here 
the remaining datadir that I'm working on :
   |-base
   |---1
   |---16384
   |---17950
   |---19621
   |---pgsql_tmp
   |-global
   |-pg_multixact
   |---members
   |---offsets
   |-pg_notify
   |-pg_stat_tmp
   |-pg_tblspc
   |-pg_xlog
   |---archive_status

Re: hardcode password in connect string

[Ricardo Martin Gomez]

Hello David,
I think so, the parameter you need in psql command is -w mipassword.
But, you have to understand that use this form or set PGPASSWORD aren't safer.
Exist a file (pg_hba.conf) for this process ir task.
Regards

Obtener Outlook para Android


From: David Gauthier 
Sent: Friday, April 13, 2018 3:51:33 PM
To: James Keener
Cc: pgsql-generallists.postgresql.org
Subject: Re: hardcode password in connect string

PGPASSWORD env var works fine.
Thanks !


On Fri, Apr 13, 2018 at 2:46 PM, James Keener 
mailto:j...@jimkeener.com>> wrote:
Is setting it as an environment variable an option? 
https://www.postgresql.org/docs/9.1/static/libpq-envars.html

Alternatively, a service file? 
https://www.postgresql.org/docs/9.0/static/libpq-pgservice.html

Jim


On April 13, 2018 2:43:01 PM EDT, David Gauthier 
mailto:davegauthie...@gmail.com>> wrote:
Hi:

PG v9.5.2 on RHEL

I like to use an alias to connect to my favorite DBs but don't like to enter 
passwords.  I used to just disable passwords (working behind a firewall), but 
this one is different.  I see nothing in the interactive connect string where I 
can enter the password...

psql -h thehost -U memyselfi mydb

Missing something like "-p mypassword"

Is there a way I can stick the pw in the linux alias definition ?

Just thinking something like this may be available since hardcoded passwords 
are supported in perl/dbi, pg driver

Thanks !

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: Can a broken Postgresql data directory be reconstructed without losing data?

[Ricardo Martin Gomez]

You must to restore a database from a backup.

Regards.

Obtener Outlook para Android


From: A A 
Sent: Sunday, April 15, 2018 5:25:05 AM
To: pgsql-general@lists.postgresql.org
Subject: Can a broken Postgresql data directory be reconstructed without losing 
data?


I'm trying to restore a database from a broken data directory that have lost 
many of its files accidentally.

I have tried unsuccessfully to install new instance of the same Postgresql 
version and then copy (with overwrite) the broken datadir to the new one. here 
the remaining datadir that I'm working on :

   |-base
   |---1
   |---16384
   |---17950
   |---19621
   |---pgsql_tmp
   |-global
   |-pg_multixact
   |---members
   |---offsets
   |-pg_notify
   |-pg_stat_tmp
   |-pg_tblspc
   |-pg_xlog
   |---archive_status

Re: cursor "x" does not exist

[rob stone]

Hello Bob,

On Sat, 2018-04-14 at 15:44 +0100, Bob Jones wrote:
> Hello,
> 
> 
> 
> P.S. As a side-question, if anyone here has experience in using
> Postgres as a backend to PHP, are refcursors the way to go or should
> I
> be thinking of SETOF or other return styles ?
> 


If you have a business requirement along the lines of "display all
outstanding orders for a customer" it is best to create a view that
serves that purpose. Then all you need to code is a "select * from
v_outstanding_orders where customer_id = $1";
If there are any outstanding orders you can page through the
associative array returned by the query in order to display the
results.

HTH,
Rob

Re: Can a broken Postgresql data directory be reconstructed without losing data?

[A A]

 Well this doesn't address the question at all.

On Sunday, April 15, 2018, 1:44:00 PM GMT+2, Ricardo Martin Gomez 
 wrote:  
 
 You must to restore a database from a backup.

Regards.

Obtener Outlook para Android
From: A A 
Sent: Sunday, April 15, 2018 5:25:05 AM
To: pgsql-general@lists.postgresql.org
Subject: Can a broken Postgresql data directory be reconstructed without losing 
data? 
I'm trying to restore a database from a broken data directory that have lost 
many of its files accidentally.

I have tried unsuccessfully to install new instance of the same Postgresql 
version and then copy (with overwrite) the broken datadir to the new one. here 
the remaining datadir that I'm working on :
   |-base
   |---1
   |---16384
   |---17950
   |---19621
   |---pgsql_tmp
   |-global
   |-pg_multixact
   |---members
   |---offsets
   |-pg_notify
   |-pg_stat_tmp
   |-pg_tblspc
   |-pg_xlog
   |---archive_status
  

Re: Can a broken Postgresql data directory be reconstructed without losing data?

[Michael Paquier]

On Sun, Apr 15, 2018 at 08:25:05AM +, A A wrote:
> I'm trying to restore a database from a broken data directory that
> have lost many of its files accidentally.

Let me guess, a wild set of rm commands?

> I have tried unsuccessfully to install new instance of the same
> Postgresql version and then copy (with overwrite) the broken datadir
> to the new one.here the remaining datadir that I'm working on :

This is the kind of things I have found myself doing some times, as well
as some people on this list, and this enters in the category of horror
stories.

So the answer to the question "Can I work on a broken data folder and
save as much data as possible?" is "yes".  However this enters in the
field of dark magics as this requires advanced skills and knowledge of
PostgreSQL, including its internals, its folder structure, and ways to
bypass some protocols or even perhaps patch the upstream code to bypass
some checks to the data you are looking for...  Additionally, this is
usually a step-by-step and case-by-case problem.

Please note that data deleted is gone.  You may be able to get back some
data from the file system which has been deleted, though this requires a
special set of skills and luck.  Hiring a professional is recommended
for such operations.
--
Michael


signature.asc
Description: PGP signature

Re: hardcode password in connect string

[Raymond O'Donnell]

On 15/04/18 12:35, Ricardo Martin Gomez wrote:

Hello David,
I think so, the parameter you need in psql command is -w mipassword.


Sorry, this is not correct. -w causes psql *never* to prompt for a 
password, and -W forces a password prompt. Neither allows the password 
to be passed as an argument - I don't think that this is possible with psql.


Details here:

  https://www.postgresql.org/docs/10/static/app-psql.html

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

Re: hardcode password in connect string

[Guillaume Lelarge]

2018-04-15 17:16 GMT+02:00 Raymond O'Donnell :

> On 15/04/18 12:35, Ricardo Martin Gomez wrote:
>
>> Hello David,
>> I think so, the parameter you need in psql command is -w mipassword.
>>
>
> Sorry, this is not correct. -w causes psql *never* to prompt for a
> password, and -W forces a password prompt. Neither allows the password to
> be passed as an argument - I don't think that this is possible with psql.
>
>
You're right, that's not possible (as an argument). And it is a security
*feature*.


> Details here:
>
>   https://www.postgresql.org/docs/10/static/app-psql.html
>
> Ray.
>


-- 
Guillaume.

Re: hardcode password in connect string

[Martín Marqués]

El 15/04/18 a las 12:16, Raymond O'Donnell escribió:
> On 15/04/18 12:35, Ricardo Martin Gomez wrote:
>> Hello David,
>> I think so, the parameter you need in psql command is -w mipassword.
> 
> Sorry, this is not correct. -w causes psql *never* to prompt for a
> password, and -W forces a password prompt. Neither allows the password
> to be passed as an argument - I don't think that this is possible with
> psql.

It is possible, you just have to construct the whole string:

psql "host=thehost dbname=mydatabase user=memyselfi password=mypassword"

Still, the best option, and the most secure, is to use a .pgpass file.
The psql invocation with it's password will very likely end in
.bash_history and alike.

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Password encryption

[Azimuddin Mohammed]

Hello,
Is there a way I can encrypt the default password column of db user
password. I know by default the password is encrypted as md5, can we
encrypt that of shadow column for password?

Thanks in advance

Re: Password encryption

[Adrian Klaver]

On 04/15/2018 05:22 PM, Azimuddin Mohammed wrote:

Hello,
Is there a way I can encrypt the default password column of db user 
password. I know by default the password is encrypted as md5, can we 
encrypt that of shadow column for password?


Are you talking about this view?:

https://www.postgresql.org/docs/10/static/view-pg-shadow.html

If so that is only readable by superusers:

production=# \c - aklaver
You are now connected to database "production" as user "aklaver".

production=> select * from pg_shadow ;
ERROR:  permission denied for relation pg_shadow

production=> \c - postgres
You are now connected to database "production" as user "postgres".

production=# select * from pg_shadow ;
usename | usesysid | usecreatedb | usesuper | userepl | 
usebypassrls |   passwd| valuntil | useconfig


...

Assuming someone is in your database as a superuser, access to the 
password field in pg_shadow is pretty much moot.




Thanks in advance



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

Re: authentication failure

[armand pirvu]

> On Apr 12, 2018, at 10:07 AM, armand pirvu  wrote:
> 
> 
>> On Apr 12, 2018, at 9:55 AM, Adrian Klaver > > wrote:
>> 
>> On 04/12/2018 07:50 AM, armand pirvu wrote:
>> 
 I would be inclined to raise the authentication_timeout first before 
 setting the auth method to trust.
 
 I would also set the below.:
 
 https://www.postgresql.org/docs/10/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
  
 
 
 log_connections (boolean)
 Causes each attempted connection to the server to be logged, as well as 
 successful completion of client authentication. Only superusers can change 
 this parameter at session start, and it cannot be changed at all within a 
 session. The default is off.
 
 log_disconnections (boolean)
 
 That will give you a better idea of what is going on connection wise.
 
 
 
 -- 
 Adrian Klaver
 adrian.kla...@aklaver.com
>>> Will do so and report back
>> 
>> Also, in a previous post you mentioned:
>> 
>> "Yes and worked fine until two days ago"
>> 
>> Is the code under version control so you can see if anything changed two 
>> days ago?
>> 
>> If not, any recollections of significant events from that time period?
>> 
>>> Many thanks
>>> Armand
>> 
>> 
>> -- 
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
> 
> No change in the code
> Only maybe the data volume and potentially the number of what we call events 
> for which that runs
> There was a talk to add some more functionality but never went through
> However I will ask around to see if someone did not forget to say about a 
> change put in place overnight (it happened before)
> 
> Thanks
> Armand
> 
> 


Sorry for the late reply. In and out sll sorts of things which do distract me.
I raised the authentication_timeout and seems okay for now

Thank you for the help

Armand

Re: authentication failure

[Adrian Klaver]

On 04/15/2018 06:22 PM, armand pirvu wrote:









Sorry for the late reply. In and out sll sorts of things which do 
distract me.

I raised the authentication_timeout and seems okay for now


I would take the 'for now' as a heads up that there is underlying issue 
that will need to be resolved eventually. Namely that the procedure is 
using enough resources to impact the authentication process.




Thank you for the help

Armand






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