Re: [ADMIN] Restore database after drop command

[Florian Weimer]

* Adarsh Sharma:

> I restore globedatabase from a .sql file on yesterday morning.I insert
> some new data in that database.
> In the evening, by mistake I issued a *drop database globedatabase* command.
>
> Today morning, I restore again the same database from backup (.sql) file.
> My .sql file have data till yesterday morning but I want newly insert
> data now. Is it possible.
>
> Is it possible to get the data back till the state before drop
> database command.

It might have been possible if you had performed a hard shutdown
directly after discovering the mistake, by undeleting the database files
at the operating system level.  This has been made more difficult
(perhaps even impossible) by your subsequent write activity.

-- 
Florian Weimer
BFK edv-consulting GmbH   http://www.bfk.de/
Kriegsstraße 100  tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

-- 
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] Restore database after drop command

[Adarsh Sharma]

I go through the link, so it is impossible to get the data back.
I have following files in my pg_xlog directory :

000100070091
000100070092
000100070093
000100070094
000100070095
000100070096
000100070097
000100070098

I think I issued the drop database command 1 month ago.
From the manual, I understand that my segment files are recycled to 
newer ones :


/The segment files are given numeric names that reflect their position 
in the abstract WAL sequence. When not using WAL archiving, the system 
normally creates just a few segment files and then "recycles" them by 
renaming no-longer-needed segment files to higher segment numbers. It's 
assumed that a segment file whose contents precede the 
checkpoint-before-last is no longer of interest and can be recycled.


/My archive_status folder is empty.
How would we know that which data these segment files corresponds too.

I followed below steps 1 month ago :
1. Load globdatabase through backup.sql (21 GB)file
2. Insert some data near about 3-4 tables ( KB) data.
3. Drop database globdatabase.
4. Load globdatabase through backup.sql (21GB)file

May be there is chance because we work very rarely on that system.
Now i have the backup file bt I want that 3-4 tables.


Thanks

Vibhor Kumar wrote:

On Jul 25, 2011, at 12:08 PM, Adarsh Sharma wrote:

  

I restore globedatabase from a .sql file on yesterday morning.I insert some new 
data in that database.
In the evening, by mistake I issued a drop database globedatabase command.
Today morning, I restore again the same database from backup (.sql) file.
My .sql file have data till yesterday morning but I want newly insert data now. 
Is it possible.
Is it possible to get the data back till the state before drop database command.



No you won't be able to recover. 


If you have Online Backup, then PITR would help you.

Thanks & Regards,
Vibhor Kumar
Blogs: http://vibhork.blogspot.com
http://vibhorkumar.wordpress.com

  

Re: [ADMIN] Restore database after drop command

[Vibhor Kumar]

On Jul 25, 2011, at 12:08 PM, Adarsh Sharma wrote:

> I restore globedatabase from a .sql file on yesterday morning.I insert some 
> new data in that database.
> In the evening, by mistake I issued a drop database globedatabase command.
> Today morning, I restore again the same database from backup (.sql) file.
> My .sql file have data till yesterday morning but I want newly insert data 
> now. Is it possible.
> Is it possible to get the data back till the state before drop database 
> command.

No you won't be able to recover. 

If you have Online Backup, then PITR would help you.

Thanks & Regards,
Vibhor Kumar
Blogs: http://vibhork.blogspot.com
http://vibhorkumar.wordpress.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] Restore database after drop command

[Adarsh Sharma]

Dear all,

I am using Postgres-8.4.2 on Windows system.
I have 2 databases in my postgres database ( globedatabase (21GB), 
urldatabase).


I restore globedatabase from a .sql file on yesterday morning.I insert 
some new data in that database.

In the evening, by mistake I issued a *drop database globedatabase* command.

Today morning, I restore again the same database from backup (.sql) file.
My .sql file have data till yesterday morning but I want newly insert 
data now. Is it possible.


Is it possible to get the data back till the state before drop database 
command.


My pglog files is in the E:/data directory & Binary log is also enabled.

Please let me know if it is possible. It's urgent.


Thanks & Regards
Adarsh Sharma

Re: [ADMIN] restore database files

[Jasen Betts]

On 2010-12-14, gosta100  wrote:
>
> Hello everyone,
>
>I have a copy of the data folder of a Windows postgres installation. Is
> it possible to restore the databases contained in there to another postgres
> server?

yes, you'll need to correct the permissions on the files and run copy them
onto an equivalent version of postgres for windows.




-- 
⚂⚃ 100% natural

-- 
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] restore database files

[Frederiko Costa]

Yes, it is. Have a look at pg_dump utility. The documentation contains
plenty of examples.

On Tue, Dec 14, 2010 at 12:14 PM, gosta100 wrote:

>
> Hello everyone,
>
>   I have a copy of the data folder of a Windows postgres installation. Is
> it possible to restore the databases contained in there to another postgres
> server?
>
> Thank you.
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/restore-database-files-tp3305210p3305210.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] restore database files

[gosta100]

Hello everyone,

   I have a copy of the data folder of a Windows postgres installation. Is
it possible to restore the databases contained in there to another postgres
server?

Thank you.
-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/restore-database-files-tp3305210p3305210.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] Restore DataBase

[Eric McKeeth]

On Tue, Jun 1, 2010 at 9:26 PM, ALEXANDER JOSE  wrote:

>   psql coon I'm trying to restore a database that has 60 GB in size, where
> there is a table with 38 million records, has raised 18 million records when
> the restore process fails on a syntax error, the backup was made with a
> file. sql as the best way I can do to restore??
>
>
It will be easier for people to help if you provide more info such as
postgres version, OS, how the backup was made, exact error message, etc.
But, if you're getting a syntax error, then my best guess would be that
either that you have a corrupt/incomplete backup, or that you're trying to
restore to a different version of postgres than you used to make the backup,
and the backup has syntax which would be valid for the version that made it,
but not for the version you're trying to restore to.

-Eric

Re: [ADMIN] Restore DataBase

[Andreas Schmitz]

hi,

sounds like a plain text backup. your problem, I guess, will be some 
constraint issue. I suggest commenting out the constraints in the backup 
file before running the restore.


regards

andreas


ALEXANDER JOSE wrote:
psql coon I'm trying to restore a database that has 60 GB in size, 
where there is a table with 38 million records, has raised 18 million 
records when the restore process fails on a syntax error, the backup 
was made with a file. sql as the best way I can do to restore??



Alexander Angel
Venezuela


Invite your mail contacts to join your friends list with Windows Live 
Spaces. It's easy! Try it! 




--
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] Restore DataBase

[Szymon Guz]

2010/6/2 ALEXANDER JOSE 

>   psql coon I'm trying to restore a database that has 60 GB in size, where
> there is a table with 38 million records, has raised 18 million records when
> the restore process fails on a syntax error, the backup was made with a
> file. sql as the best way I can do to restore??
>
>
> Alexander Angel
> Venezuela
>


How did you make the backup?


regards
Szymon Guz

[ADMIN] Restore DataBase

[ALEXANDER JOSE]

psql coon I'm trying to restore a database
 that has 60 GB in size, where there is a table with 38 million records,
 has raised 18 million records when the restore process fails on a 
syntax error, the backup was made with a file. sql as the best way I can do 
to restore??


Alexander Angel
Venezuela





  
_
Invite your mail contacts to join your friends list with Windows Live Spaces. 
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us

Re: [ADMIN] Restore database in postgres 8.4 linux

[Alex Hunsaker]

On Wed, May 19, 2010 at 16:25, ALEXANDER JOSE  wrote:
> I have a question, I have postgres 8.2.5 on a windows server on another
> machine and I have suse linux enterprise 10 postgres 8.4, you perform a
> backup with pgadmin 1.10 to a database and restore the database in the
> version of postgres 8.4 gave me many errors that could not find some
> functions.

Well, you wont get much here unless you paste some of the actual errors.

My guess its its related to full text searching (in which case you
will want to read
http://www.postgresql.org/docs/8.4/static/textsearch-migration.html).
Otherwise its probably some other contrib or 3rd party module you
forgot to install.

Also, I don't know much about pgadmin but make sure it is using 8.4s
version of pg_dump/pg_restore.

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

[ADMIN] Restore database in postgres 8.4 linux

[ALEXANDER JOSE]

I have a question, I 
have postgres 8.2.5 on a windows server on another machine and I have 
suse linux enterprise 10 postgres 8.4, you perform a backup with pgadmin
 1.10 to a database and restore the database in the version of postgres 8.4 
gave me many errors that could not find some functions.



Atentamente

DBA Alexander Angel
Venezuela
  
_
Invite your mail contacts to join your friends list with Windows Live Spaces. 
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us

Re: [ADMIN] Restore database from tablespace

[Greg Spiegelberg]

On Thu, Jan 28, 2010 at 4:26 PM, Kevin Grittner
 wrote:
>
> I'm still not sure I follow, but there's a technique which isn't
> worth much for backup proper, but can be a good way to repeatedly
> get to a consistent starting point for tests in some circumstances.
> Look at CREATE DATABASE x WITH TEMPLATE y.
>
> http://www.postgresql.org/docs/8.4/interactive/sql-createdatabase.html
>
> You can do that once to capture a starting point for testing.  You
> can drop the testing database and re-create at will.

I agree, CREATE DATABASE WITH TEMPLATE isn't worth much for backups.
I was imagining a feature that could be used for backups as well as
other applications.

> If this and the various backup techniques don't do what you want,
> I'm at a loss.  I think you'd need to post something a bit more
> concrete for me to understand what you mean.

Okay.  The analogy I'd make is file system / disk volume snapshots.  I
do not know your level of understanding here so forgive my
description.

File system / disk volume snapshots are instantaneous and immediately
available because a complete copy wasn't made.  The way file system
snapshots are accomplished is by a driver that understands the copy is
a snapshot of an original and while the snapshot exists any
modification to the original is permissible however the data at the
time of the snapshot is preserved.  Here is a simple example:

 1. Snapshot data-monday is made of the file system /data on Monday
 2. data-monday is mounted by the system under /data-monday
 3. Any access to /data-monday is "redirected" to the original /data
unless a modification to the original exists
 4. Modification to the original: the original file /data/X is
modified and saved in /data however the original is then copied to the
snapshot data-monday
 5. Remove the snapshot data-monday and the original file system is still intact

I'm imagining a feature where an admin could CREATE SNAPSHOT x OF [
DATABASE | SCHEMA | TABLE ] y; that uses a similar technique that I
described above.  Now, if a table is modified I wouldn't imagine the
entire original table / index being copied but rather just the
modified rows.  This would be like disk volume snapshots that worry
about modifications to blocks rather than files.

 1. Snapshot data-vol-monday is created of the disk volume data-vol
containing the file system /data on Monday
 2. The file system in data-vol-monday is mounted by the system under
/data-monday
 3. Any access to /data-monday is "redirected" to the original /data
unless a modification to the original exists
 4. Modification to the original: the original file /data/X is
modified in block 3 of 10 and saved in /data however the original 3rd
block of X is then copied to the snapshot data-vol-monday
 5. Remove the snapshot data-vol-monday and the original disk volume
and file system is still intact

The snapshots could be used for backups, testing, audit, or even
active production access.  Another application of snapshots is being
able to merge them back to the original.  Instead of step 5 above
where the snapshot is removed a command could be issued to apply any
changes back to the original.  Using the snapshot instead of original
also has the benefit of reducing I/O overhead.

I don't make lite of the feature.  If done or even considered it'd
surely be a large undertaking and have performance implications with
the additional I/O but the applications are as great or greater than
file system and disk volume snapshots.

Greg

-- 
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] Restore database from tablespace

[Kevin Grittner]

Greg Spiegelberg  wrote:
 
> I don't know if this was the intent but it did provide me with
> what I thought was a good idea.
>  * Snapshot a schema / tablespace / database to another schema /
> tablespace / database.
> 
> The applications for this could be
>  * an alternative to the existing backup methods
>  * using the copy for testing purposes without having to
>create-drop database
>  * providing stock data for application demos without fear of
>losing the original
>  * quick recovery to a known good schema and data
 
I'm still not sure I follow, but there's a technique which isn't
worth much for backup proper, but can be a good way to repeatedly
get to a consistent starting point for tests in some circumstances. 
Look at CREATE DATABASE x WITH TEMPLATE y.
 
http://www.postgresql.org/docs/8.4/interactive/sql-createdatabase.html
 
You can do that once to capture a starting point for testing.  You
can drop the testing database and re-create at will.
 
If this and the various backup techniques don't do what you want,
I'm at a loss.  I think you'd need to post something a bit more
concrete for me to understand what you mean.
 
-Kevin

-- 
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] Restore database from tablespace

[Greg Spiegelberg]

On Thu, Jan 28, 2010 at 9:56 AM, Kevin Grittner
 wrote:
> "dev.pho"  wrote:
>
>> I was wondering if I could restore the database from the location
>> of the tablespace.
>
> It's not clear exactly what you want to do.  Have you read through
> the documentation on backup and restore?:

I don't know if this was the intent but it did provide me with what I
thought was a good idea.
 * Snapshot a schema / tablespace / database to another schema /
tablespace / database.

The applications for this could be
 * an alternative to the existing backup methods
 * using the copy for testing purposes without having to create-drop database
 * providing stock data for application demos without fear of losing
the original
 * quick recovery to a known good schema and data

Could the mechanics behind pg_start_backup() assist with the
development of such a feature?

It's just a rough idea.

Greg

-- 
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] Restore database from tablespace

[Kevin Grittner]

"dev.pho"  wrote:
 
> I was wondering if I could restore the database from the location
> of the tablespace.
 
It's not clear exactly what you want to do.  Have you read through
the documentation on backup and restore?:
 
http://www.postgresql.org/docs/8.4/interactive/backup.html
 
Perhaps you want to follow the instructions for "File System Level
Backup", or maybe PITR?
 
-Kevin

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

[ADMIN] Restore database from tablespace

[dev.pho]

Hello,

I was wondering if I could restore the database from the location of the
tablespace.

If yes, how can I do this.

Thanks in advance,

Dev.

Re: [ADMIN] Restore Database From data folder

[Chander Ganesan]

[EMAIL PROTECTED] wrote:

i have a backup of data folder of my old  postgresql database on 7.54

now i want to restore my complete database from data folder in ver 8.1

I assume you mean you wish to upgrade your 7.4 installation of 
PostgreSQL to PostgreSQL 8.1 ?


The short answer:

You'll need to start the 7.4 version of the server, with your old data 
directory and run the version of 'pg_dumpall' that comes with PostgreSQL 
8.1 .  Once you've generated a dump and saved it, you can install 8.1 
and then use the 'psql' utility to re-load the dump on 8.1 .  The 7.4 
data directory isn't compatible with 8.1, so you'll need to perform the 
dump and subsequent restore.


You'll probably also want to go through the 7.4 pg_hba.conf and 
postgresql.conf files and port over any changes you need that are 8.1 
compatible.


--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
919-463-0999/877-258-8987
http://www.otg-nc.com
Expert PostgreSQL & PostGIS training - delivered worldwide.


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

[ADMIN] Restore Database From data folder

[yogesh]

hi alli have a backup of data folder of my old  postgresql
database on 7.54now i want to restore my complete database
from data folder in ver 8.1so can any gurus give me the idea
about itYogeshSenior Officer (System)

-- 
This message has been scanned for viruses and
dangerous content by
BanasDairy(Amul), and is
believed to be clean.

[ADMIN] Restore database

[Alexander Burbello]

I need to test and create a procedure to restore databases.
I followed the steps based on the site, but I couldn't finish succesfully.

I did:

 1. Put the database on Backup Mode and copy datafiles.
/pg/bin/psql cresoldev -c "SELECT pg_start_backup('/pg/backup/');"
tar -cvf /pg/backup/bk_base.tar /pg/data/base/*
/pg/bin/psql cresoldev -c "SELECT pg_stop_backup();"

File .conf: archive_command = 'cp -i %p /pg/backup/xlog/%f 

Re: [ADMIN] Restore Database

[Alexander Burbello]

Yes!
I followed exactly that page to do.

Thanks



Scott Marlowe escreveu:


On Wed, 2006-07-12 at 15:04, Alexander Burbello wrote:
 

Ok! Its a good tool, but for Production Database I think it is not 
recommended.

Only using pg_dump for the second backup plain.
Suppose that you backed up at 6:00am and at 9am happened a crash on the 
server.

In this case, I would lost data between that time, 3 hours of information.

For production databases, my plan is to do phisical backup including WAL.
If a crash happen, I can restore the datafiles and recover applying the 
WAL logs until the last file was generated.

As Oracle does in this type of crash.

My doubt is that I am not getting apply the WAL files on recover stage.

Any other suggestion?
   



You have read this section of the manual, right?

http://www.postgresql.org/docs/8.1/interactive/backup-online.html



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

 




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [ADMIN] Restore Database

[Scott Marlowe]

On Wed, 2006-07-12 at 15:04, Alexander Burbello wrote:
> Ok! Its a good tool, but for Production Database I think it is not 
> recommended.
> Only using pg_dump for the second backup plain.
> Suppose that you backed up at 6:00am and at 9am happened a crash on the 
> server.
> In this case, I would lost data between that time, 3 hours of information.
> 
> For production databases, my plan is to do phisical backup including WAL.
> If a crash happen, I can restore the datafiles and recover applying the 
> WAL logs until the last file was generated.
> As Oracle does in this type of crash.
> 
> My doubt is that I am not getting apply the WAL files on recover stage.
> 
> Any other suggestion?

You have read this section of the manual, right?

http://www.postgresql.org/docs/8.1/interactive/backup-online.html



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [ADMIN] Restore Database

[Alexander Burbello]

Ok! Its a good tool, but for Production Database I think it is not 
recommended.

Only using pg_dump for the second backup plain.
Suppose that you backed up at 6:00am and at 9am happened a crash on the 
server.

In this case, I would lost data between that time, 3 hours of information.

For production databases, my plan is to do phisical backup including WAL.
If a crash happen, I can restore the datafiles and recover applying the 
WAL logs until the last file was generated.

As Oracle does in this type of crash.

My doubt is that I am not getting apply the WAL files on recover stage.

Any other suggestion?

Thanks for your help




Aaron Bono escreveu:

On 7/11/06, *Burbello* <[EMAIL PROTECTED] 
> wrote:


I need to test and create a procedure to restore
databases.


Why not just use pg_dump?

See http://manual.intl.indoglobal.com/ch06s07.html 
 - it's really easy.  
This is how we copy from production to testing and development and how 
we do nightly backups.



==
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
== 




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [ADMIN] Restore Database

[Aaron Bono]

On 7/11/06, Burbello <[EMAIL PROTECTED]> wrote:
I need to test and create a procedure to restoredatabases.Why not just use pg_dump?See http://manual.intl.indoglobal.com/ch06s07.html
 - it's really easy.  This is how we copy from production to testing and development and how we do nightly backups. ==   Aaron Bono
   Aranya Software Technologies, Inc.   http://www.aranya.com==

[ADMIN] Restore Database

[Burbello]

I need to test and create a procedure to restore
databases.
I followed the steps based on the site, but I couldn't
finish succesfully.

I did:

 1. Put the database on Backup Mode and copy
datafiles.
/pg/bin/psql cresoldev -c "SELECT
pg_start_backup('/pg/backup/');"
tar -cvf /pg/backup/bk_base.tar /pg/data/base/*
/pg/bin/psql cresoldev -c "SELECT
pg_stop_backup();"

File .conf: archive_command = 'cp -i %p
/pg/backup/xlog/%f http://mail.yahoo.com.br/

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [ADMIN] restore database from bare files

[jehan procaccia]

thanks that was in the doc ! indeed I needed to put "local ezpublish_db 
ezpublish trust"  before other rules , as the first rule that match  end 
the reading of pg_hba.conf .


Thanks again .

Ben Kim wrote:


psql: FATAL:  IDENT authentication failed for user "ezpublish"
   



This might help, or you may want to check or post your pg_hba.conf.

http://archives.postgresql.org/pgsql-sql/2004-03/msg00202.php

(or 
http://www.postgresql.org/docs/7.4/interactive/auth-methods.html#AUTH-IDENT)


HTH,

Ben Kim / Developer 
College of Education 
Texas A&M University





---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match
 




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] restore database from bare files

[Ben Kim]

>psql: FATAL:  IDENT authentication failed for user "ezpublish"

This might help, or you may want to check or post your pg_hba.conf.

http://archives.postgresql.org/pgsql-sql/2004-03/msg00202.php

(or 
http://www.postgresql.org/docs/7.4/interactive/auth-methods.html#AUTH-IDENT)

HTH,

Ben Kim / Developer 
College of Education 
Texas A&M University




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [ADMIN] restore database from bare files

[jehan procaccia]

Ben Kim wrote:


ezpublish_db-# ALTER USER  ezpublish SET PASSWORD secret;
ERROR:  syntax error at or near "$" at character 1
   



I wonder why you have "ezpublish_db-#" instead of "ezpublish_db=#"? I just
noticed it, and to me it happens usually when something's been carried
over from the previous line. My 2 pence...
 

indeed I typed a comment before without ending it with ";" that might 
explain the "syntax error" !
for my pb, I have part of an explanation -> I forgot to recreate the 
user ! so here I go again in one command to create user and set password:

$ createuser ezpublish -P
Enter password for new user: ***
Enter it again: ***
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
then:
$ psql ezpublish_db -U ezpublish -W
Password: ***
psql: FATAL:  IDENT authentication failed for user "ezpublish"
I really don't understand :-(
If I go with postgres user, no pb:
$ psql ezpublish_db -U postgres
Welcome to psql 7.4.8, the PostgreSQL interactive terminal.

if it can help to debug, here's my users list:
ezpublish_db=# select * from pg_user;
usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | 
valuntil | useconfig
---+--+-+--+---+--+--+--- 


postgres  |1 | t   | t| t |  |  |
ezpublish |  100 | t   | f| f |  |  |
(2 rows)

If you have an idea , I'll really apreciate

Thanks !.



Regards,

Ben Kim
Developer
College of Education 
Texas A&M University



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
 




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] restore database from bare files

[Ben Kim]

>ezpublish_db-# ALTER USER  ezpublish SET PASSWORD secret;
>ERROR:  syntax error at or near "$" at character 1

I wonder why you have "ezpublish_db-#" instead of "ezpublish_db=#"? I just
noticed it, and to me it happens usually when something's been carried
over from the previous line. My 2 pence...


Regards,

Ben Kim
Developer
College of Education 
Texas A&M University


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] restore database from bare files

[jehan-free]

very good, that worked fine :-)
I restored the files from tar, started a postgresql 7.3 on an old redhat 
9 ! pg_dump my database, psql it back to my postgresql 7.4 on my 
production RHEL4 server .


still a small pb, I seem to have lost authentification. (although 
pg_hba.conf was restore also)

$ psql -h meta1 -U ezpublish -d ezpublish_db
psql: FATAL:  user "ezpublish" does not exist
if I go with:
$ psql -h meta1 -U postgres -d ezpublish_db
Welcome to psql 7.4.8, the PostgreSQL interactive terminal.
that works , then I go for setting a user + password:
ezpublish_db-# ALTER USER  ezpublish SET PASSWORD secret;
ERROR:  syntax error at or near "$" at character 1
what's wrong ?
note that for that ezpublisher database I had initily integrated from 
postgresql-contribs those functions:

$psql ezpublish_db < /usr/share/pgsql/contrib/pgcrypto.sql
don't know if my problem is related to that ?

Thanks again.
Martin Fandel wrote:


Hi,

try this:
psql -t -d yourdb -c "SELECT datid FROM pg_stat_database WHERE
datname='yourdb';"

http://www.postgresql.org/docs/8.0/static/monitoring-stats.html

Greetings,

Martin

Am Donnerstag, den 30.06.2005, 12:57 +0200 schrieb jehan-free:
 

OK, I'am not yet at restarting postgres .. but if at get pb then I check 
that , thanks !
For now , How can I tell from the bare file the mapping between a 
database name and the number appearing in /var/lib/pgsql/base directory

I have :
pgsql/data/base/1/
pgsql/data/base/16975/
pgsql/data/base/16980/
so I made the assumption that 1 is a database (probably the test initial 
database ?), 16975 is an other one and 16980 a tird one ! How can I find 
the map from  these numbers to database name ?

thanks
Martin Fandel wrote:

   


Hi

Maybe you must reset the WAL's
( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html )
after restoring from tarball if postgres doesn't start.

Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan:


 

So I must one way or another run a 7.3, restore the file from the 
tarball as is (just put them back to /var/lib/pgsql), the databases 
should be running correctly then (?), then pg_dump it , upgrade to 7.4 
and restore from the pg_dump .
before running in all this (and I still don't know how I will be able to 
get a 7.3 on RHEL4 ... ?) is that the correct procedure ?

thanks
  

   


yes, this is the correct way :).

Martin


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
 choose an index scan if your joining column's datatypes do not
 match


 




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
 




---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] restore database from bare files

[Martin Fandel]

Hi,

try this:
psql -t -d yourdb -c "SELECT datid FROM pg_stat_database WHERE
datname='yourdb';"

http://www.postgresql.org/docs/8.0/static/monitoring-stats.html

Greetings,

Martin

Am Donnerstag, den 30.06.2005, 12:57 +0200 schrieb jehan-free:
> OK, I'am not yet at restarting postgres .. but if at get pb then I check 
> that , thanks !
> For now , How can I tell from the bare file the mapping between a 
> database name and the number appearing in /var/lib/pgsql/base directory
> I have :
> pgsql/data/base/1/
> pgsql/data/base/16975/
> pgsql/data/base/16980/
> so I made the assumption that 1 is a database (probably the test initial 
> database ?), 16975 is an other one and 16980 a tird one ! How can I find 
> the map from  these numbers to database name ?
> thanks
> Martin Fandel wrote:
> 
> >Hi
> >
> >Maybe you must reset the WAL's
> >( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html )
> >after restoring from tarball if postgres doesn't start.
> >
> >Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan:
> >  
> >
> >>So I must one way or another run a 7.3, restore the file from the 
> >>tarball as is (just put them back to /var/lib/pgsql), the databases 
> >>should be running correctly then (?), then pg_dump it , upgrade to 7.4 
> >>and restore from the pg_dump .
> >>before running in all this (and I still don't know how I will be able to 
> >>get a 7.3 on RHEL4 ... ?) is that the correct procedure ?
> >>thanks
> >>
> >>
> >
> >yes, this is the correct way :).
> >
> >Martin
> >
> >
> >---(end of broadcast)---
> >TIP 9: In versions below 8.0, the planner will ignore your desire to
> >   choose an index scan if your joining column's datatypes do not
> >   match
> >  
> >
> 


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] restore database from bare files

[jehan-free]

OK, I'am not yet at restarting postgres .. but if at get pb then I check 
that , thanks !
For now , How can I tell from the bare file the mapping between a 
database name and the number appearing in /var/lib/pgsql/base directory

I have :
pgsql/data/base/1/
pgsql/data/base/16975/
pgsql/data/base/16980/
so I made the assumption that 1 is a database (probably the test initial 
database ?), 16975 is an other one and 16980 a tird one ! How can I find 
the map from  these numbers to database name ?

thanks
Martin Fandel wrote:


Hi

Maybe you must reset the WAL's
( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html )
after restoring from tarball if postgres doesn't start.

Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan:
 

So I must one way or another run a 7.3, restore the file from the 
tarball as is (just put them back to /var/lib/pgsql), the databases 
should be running correctly then (?), then pg_dump it , upgrade to 7.4 
and restore from the pg_dump .
before running in all this (and I still don't know how I will be able to 
get a 7.3 on RHEL4 ... ?) is that the correct procedure ?

thanks
   



yes, this is the correct way :).

Martin


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match
 




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] restore database from bare files

[Martin Fandel]

Hi

Maybe you must reset the WAL's
( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html )
after restoring from tarball if postgres doesn't start.

Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan:
> So I must one way or another run a 7.3, restore the file from the 
> tarball as is (just put them back to /var/lib/pgsql), the databases 
> should be running correctly then (?), then pg_dump it , upgrade to 7.4 
> and restore from the pg_dump .
> before running in all this (and I still don't know how I will be able to 
> get a 7.3 on RHEL4 ... ?) is that the correct procedure ?
> thanks

yes, this is the correct way :).

Martin


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [ADMIN] restore database from bare files

[jehan]

So I must one way or another run a 7.3, restore the file from the 
tarball as is (just put them back to /var/lib/pgsql), the databases 
should be running correctly then (?), then pg_dump it , upgrade to 7.4 
and restore from the pg_dump .
before running in all this (and I still don't know how I will be able to 
get a 7.3 on RHEL4 ... ?) is that the correct procedure ?


thanks

Dario a écrit :


Hello. Sorry for mi inglish! :-)

Postgres 7.4 will not access 7.3 PGDATA repository. You must downgrade your
binary files to any 7.3.X (latest is better)
Note: 7.3 won't access 7.4 files neither. Yo can only do sub-sub-version
updates without having to dump and restore (example 7.3.1. to 7.3.9 doesn't
need to dump and restore of databases)

greeting.

-Mensaje original-
De: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] nombre de Peter Eisentraut
Enviado el: miércoles, 29 de junio de 2005 15:48
Para: jehan procaccia
CC: pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] restore database from bare files


jehan procaccia wrote:
 


However I do have a tar file of the filesystem , it was on a RHEL 3
with rh-postgresql-server-7.3.9-2 and I have a tar of /var/lib/pgslq
will just restoring the whole directory /var/lib/pgsql will suffices
   



Assuming that you made the tarball when the server was shut down, just
restore it and you should be all set.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq
 




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] restore database from bare files

[Dario]

Hello. Sorry for mi inglish! :-)

Postgres 7.4 will not access 7.3 PGDATA repository. You must downgrade your
binary files to any 7.3.X (latest is better)
Note: 7.3 won't access 7.4 files neither. Yo can only do sub-sub-version
updates without having to dump and restore (example 7.3.1. to 7.3.9 doesn't
need to dump and restore of databases)

greeting.

-Mensaje original-
De: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] nombre de Peter Eisentraut
Enviado el: miércoles, 29 de junio de 2005 15:48
Para: jehan procaccia
CC: pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] restore database from bare files


jehan procaccia wrote:
> However I do have a tar file of the filesystem , it was on a RHEL 3
> with rh-postgresql-server-7.3.9-2 and I have a tar of /var/lib/pgslq
> will just restoring the whole directory /var/lib/pgsql will suffices

Assuming that you made the tarball when the server was shut down, just
restore it and you should be all set.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [ADMIN] restore database from bare files

[Peter Eisentraut]

jehan procaccia wrote:
> However I do have a tar file of the filesystem , it was on a RHEL 3
> with rh-postgresql-server-7.3.9-2 and I have a tar of /var/lib/pgslq
> will just restoring the whole directory /var/lib/pgsql will suffices

Assuming that you made the tarball when the server was shut down, just 
restore it and you should be all set.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[ADMIN] restore database from bare files

[jehan procaccia]

hello,

I crashed my server, and I didn't took the precaution to pg_dump one of 
my database :-(
However I do have a tar file of the filesystem , it was on a RHEL 3 with 
rh-postgresql-server-7.3.9-2 and I have a tar of /var/lib/pgslq
will just restoring the whole directory /var/lib/pgsql will suffices ? 
or should I do some tricky operations ?
Here's what I have for example ..., how do I match a database name to 
the number -> here 16975 is which database ?:

drwx-- / 0 2005-03-08 17:38:24 pgsql/data/base/16975/
-rw--- /475136 2005-03-08 17:38:24 
pgsql/data/base/16975/1255
-rw--- / 32768 2005-03-08 17:38:24 
pgsql/data/base/16975/1247

and
-rw--- / 0 2005-03-08 17:38:22 pgsql/data/global/16675
and
drwx-- / 0 2005-05-31 18:14:51 pgsql/data/pg_xlog/
-rw--- /  16777216 2005-05-31 18:08:53 
pgsql/data/pg_xlog/0011
-rw--- /  16777216 2005-06-28 15:33:10 
pgsql/data/pg_xlog/0010

drwx-- / 0 2005-06-20 14:54:12 pgsql/data/pg_clog/
-rw--- /262144 2005-06-01 18:15:07 pgsql/data/pg_clog/
-rw--- /262144 2005-06-20 14:49:10 pgsql/data/pg_clog/0001
-rw--- / 32768 2005-06-28 15:10:19 pgsql/data/pg_clog/0002
and config files
-rw--- /  3080 2005-04-22 18:33:17 pgsql/data/pg_hba.conf
-rw--- /  1441 2005-03-08 17:38:22 pgsql/data/pg_ident.conf
-rw--- /  5331 2005-03-25 15:19:47 
pgsql/data/postgresql.conf
-rw--- /59 2005-06-28 15:27:14 
pgsql/data/postmaster.opts


The crashed happened during an upgrade to RHEL 4 , so now on RHEL 
postgresql version will be  postgresql-server-7.4.7-2.RHEL4.1 
! 
any incompatibility or migration from 7.3.9-2 ?

thanks.




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings