Tuesday, 9 March 2010
|
| restore from a pg_dumpall file Bob Powell 19:52:41 |
| | ** High Priority **
Hello everyone,
I have a restore file created by pg_dumpall. The restore file has two users associated with ownership of tables. The postgres user and a user called nsadmin.
When I attempt to restore this it won't create my call_handlers because it's not the postgres user. If I attempt to restore under the nsadmin user the language handlers fail.
How can I restore this file without changing everything to postgress user?
Thanks.
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
| | 2 answer | Add comment |
|
| pg_dump and restore - views Marcus Wang 19:50:59 |
| | Hi all, We've got postgresql 7.4 and a few views. I'm using pg_dump to backup the database and everything is ok. Recently, we added 5 new views - but when I restore the dump with psql, out of 5 new views, only 3 is recreated, 2 are missing. But when I check the dump file - those views are in the dump, but somehow they are not recreated. While the database is being restored - I've got error messages for relattioni "view_name" does not exist. I even tried with pg_dumpall and still got the same result - any ideas to troubleshoot?
Thanks
|
| | 3 answer | Add comment |
|
| data restore Jodi Kanter 19:49:55 |
| | Are there limits to how much can be restored using the \i switch is psql? I have been doing dumps for some time now using copy commands instead of inserts. I then go in to psql to run my backup script using \i. I am now having issues all of a sudden with it hanging. Maybe I ought to attempt the dump and restore another way? Thanks Jodi --
/_______________________________ //Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 jkanter@virginia.edu <mailto:jkanter@virginia.edu>/
/ /
/ /
|
| | 6 answers | Add comment |
|
| RESTORE Error Jeanna Geier 19:48:39 |
| | Hi List!
I'm attempting to do a restore of a database and I'm getting the following error the I'm not familiar with:
COPY workorder (work_order_id, projectname, request_date, requestor, request_detail, request_completion_date, request_a... pg_restore: restoring data for table "worksite" pg_restore: [archiver (db)] Error from TOC entry 2584; 0 6536819 TABLE DATA worksite postgres pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "255454" at character 1 Command was: 2554543WestSalem2006-05-12 14:10:18.578-05gregclean it 2006-05-12 17:00:00-05W6037 Ruth LaneOnalaskaWisconsin54650... pg_restore: restoring data for table "BLOBS" pg_restore: restored 0 large objects WARNING: errors ignored on restore: 73
Process returned exit code 1.
The '255454' here is in the OID column of the table...
And here's the CREATE stmt for the workorder table:
CREATE TABLE workorder.workorder ( work_order_id serial NOT NULL, projectname varchar NOT NULL, request_date timestamptz, requestor varchar NOT NULL, request_detail varchar NOT NULL, request_completion_date timestamptz, request_address varchar, request_city varchar, request_state varchar, request_zip varchar, request_location_description varchar, request_floor varchar, request_department varchar, request_priority varchar, request_health_security_concern varchar, request_vandalism varchar, request_recurring_interval varchar, request_roomid varchar, request_tagid varchar, request_presence_required bool, request_available_date timestamptz, site_name varchar NOT NULL, authorizer varchar, date_authorized timestamptz, elementid int8, element_description varchar, priority varchar, work_detail varchar, est_start_time timestamptz, actual_start_time timestamptz, est_completion_time timestamptz, actual_completion_time timestamptz, actual_labor_cost numeric, actual_material_cost numeric, comments varchar, recurrence_interval int8, account_name varchar, CONSTRAINT pk_workorder PRIMARY KEY (work_order_id), CONSTRAINT "Relationship238" FOREIGN KEY (projectname) REFERENCES project (projectname) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT "Relationship245" FOREIGN KEY (requestor) REFERENCES projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT "Relationship246" FOREIGN KEY (authorizer) REFERENCES projectuser (userid) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT "Relationship254" FOREIGN KEY (projectname, account_name) REFERENCES projectdata.account (projectname, account_name) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT "Relationship265" FOREIGN KEY (projectname, site_name) REFERENCES workorder.worksite (projectname, site_name) ON UPDATE CASCADE ON DELETE RESTRICT )
Any thoughts or ideas on the error? Thanks in advance for your help! -Jeanna
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
|
| | 6 answers | Add comment |
Monday, 15 February 2010
|
| pg_dump/pg_restore Alexey Bobkov 12:33:47 |
| | PostgreSQL 7.3.2
I have been dumping my database with next options: pg_dump -f /path_to_database/backup/db_backup.file -Z 9 database_name and get db_backup.file file.
Then a try to restore my data: pg_restore /path_to_database/backup/db_backup.file and get next error pg_restore: [archiver] input file does not appear to be a valid archive
I think it's because in pg_dump, I not set -F c option
How I can restore this broken file?
Thanks
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
|
| | 9 answers | Add comment |
|
| Problem performing a restore of a data schema in Windows Shaun Clements 12:31:58 |
| | Hi All
Im having a problem restoring a data schema on Postgres 8.01 for Windows. Im using the following command psql -U username -d db1 > filename.dmp
This should have restored the schema from the file, filename.dmp to the database, db1 I did create the database before restoring the data schema. And its not working.
Am I missing something? Thanks in advance.
Kind Regards, Shaun Clements Subject to www.relyant.co.za/edisclaim.htm
|
| | 4 answer | Add comment |
|
| dependency tracking of functions & schemas , problem in dump and restore. Guest 12:29:28 |
| | Hi,
CREATE FUNCTION checks properly for the existance of referred schemas.
Eg
CREATE FUNCTION current_date_id(timestamp) returns integer AS 'SELECT date_id from general.date_dimension where sql_date=date($1)' language 'SQL';ERROR: Namespace "general" does not exist
but DROP SCHEMA does not check that some function could depend on a table that is potentially getting dropped.
If such functions remains even when corresponding schemas are dropped they do not pass the dump restore cycle.
Even if the schema are not dropped pg_dump [ 7.4dev ] dumps out these functions before the SCHEMA creation hence restore fails.
regds Mallah.
----------------------------------------- Over 1,00,000 exporters are waiting for your order! Click below to get in touch with leading Indian exporters listed in the premier trade directory Exporters Yellow Pages. http://www.trade-india.com/dyn/gdh/eyp/
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
|
| | 1 answer | Add comment |
Saturday, 13 February 2010
|
| datediff is there something like it? Joel Fradkin 18:28:48 |
| | Hi all working my way through our views and all is going very well.
We use datediff in MSSQL a bit and I read about the field1::date - field2::date to return the days numerically.
Is there any way to get months and years besides guessing days / 30 for months etc?
Joel Fradkin
Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305
jfradkin@wazagua.com www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments.
|
| | 5 answers | Add comment |
Thursday, 11 February 2010
|
| The database system is in recovery mode Trevor Astrope 18:18:19 |
| | Our database just experienced the problem in the subject line. After the error, the database was still up, but would issue the error to any new connections. The stats collector process, a vacuum and one other connection were all in an uninterruptable state and the machine had to be rebooted.
Could this be the linux kernel randomly killing processes under heavy load issue? I've seen that happen on other machines before, but in those cases the kernel logged when it was killing processes in syslog... There were no messages in syslog in this case.
System is postgresql 7.2.1 on redhat 7.2. Here's the logs:
2003-05-01 16:54:08 DEBUG: server process (pid 2599) was terminated by signal 11 2003-05-01 16:54:08 DEBUG: terminating any other active server processes 2003-05-01 16:54:08 NOTICE: Message from PostgreSQL backend: The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query.
After a bunch of these, the database goes in recovery mode:
2003-05-01 16:54:08 FATAL 1: The database system is in recovery mode
Then after the machine is rebooted and while it is starting up, there is these messages:
2003-05-01 17:35:49 DEBUG: ReadRecord: unexpected pageaddr 21/37D94000 in log file 33, segment 63, offset 14237696 2003-05-01 17:35:49 DEBUG: redo done at 21/3FD92564
I presume this is rerunning the WAL? Is the message serious...could there be database corruption or just lost transactions?
Thanks for any help.
Regards,
Trevor Astrope astrope@e-corp.net
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
|
| | 12 answers | Add comment |
Wednesday, 10 February 2010
|
| pg_dumpall pg_restore Rob Sell 16:29:57 |
| | Ok this should be simple but I am missing something here.
Yesterday I upgrade to 7.4 before I do that I run
$pg_dumpall > /tmp/pg_keep
When I try to restore off this file I get pg_restore: [archiver] input file does not appear to be a valid archive
So I tar the file and it appears to be a valid archive now.. Now when I try to restore I get the error:
pg_restore: [tar archiver] could not find header for file toc.dat in tar archive
What am I missing here? Or is my dump just not valid and I am out of luck.
Btw I did the dump with the 7.3 tools and now am using the 7.4 restore if that makes any difference.
Rob
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
| | 3 answer | Add comment |
|
| Database backup and restore Colin Stearman 16:28:07 |
| | Am I missing something? There seems to be no way to dump all databases and then restore them (as you'd have to do on a version change) if the databases contain blobs.
pg_dump supports dumping of blobs with the -b -Ft switch, but using pg_dumpall with those switches doesn't seem to work. And in any case, pg_restore seems to be missing its equivalent pg_restoreall. Other than (painfully) manually pg_dumping and pg_restoring each database, how else could it be done?
I would search the mailing lists but the site's search engine is down.
Thanks, Colin --------------------------------------------- Colin Stearman President Ashdown Technologies 119 E. Main St. Milford, MA 01757 USA Tel: +1 508-478-1234 Fax: +1 508-478-1244 www.ashdowntech.com
|
| | 9 answers | Add comment |
Sunday, 24 January 2010
|
| Fast backup/restore Gandalf 16:21:00 |
| | I am looking for a *fast* backup/restore tools for Postgres. I've found the current used tools pg_dump and pg_restore to be very slow on large databases (~30-40GB). Restore takes time in the tune of 6 hrs on a Linux, 4 proc, 32 G RAM machine which is not acceptable.
I am using "pg_dump -Fc" to take backup. I understand binary compression adds to the time, but there are other databases (like DB2) which take much less time on similar data sizes.
Are there faster tools available?
Thanks.
|
| | 9 answers | Add comment |
Saturday, 23 January 2010
|
| Restore question Steve Doerr 15:41:18 |
| | I've recently had to restore a couple of db's from backup and there were obsolete employee tables in the dbs.
Now I can't drop or rename this or any other tables to properly load new ones.
sqltest=# drop table employee sqltest-#
I used pg_dump on the two dbs, and restored with the following: psql -f /home/steve/080502sltestdb.out sqltest
Psql is also broken for the next command after one of these failed drops (when it's showing the - instead of =):
sqltest-# select * from employee; ERROR: parser: parse error at or near "select"
try again and:
sqltest=# select * from employee; id | employee_id | name | addr1 | addr2 | addr3 | workphone | homephone | grade | title_position | salary | salarytype | exemptcode | gov_id | email | startdate | enddate | notes
Does anyone recognize what's broken here or know what I might be able to do to fix it?
Thanks for any assistance. Steve
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
| | 2 answer | Add comment |
Friday, 20 November 2009
|
| iplike.so access denied help!! Faqeer ALI 19:59:10 |
| |
i have installed pgsql8.* on fc3 .. it has installed well and test was successful. while installing opennms i run the installer #./installer -disU.. this lead to ca certain error regarding iplike.so permission denied.
error message
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: could not access file "/root/Desktop/downloadable/opennms-1.2.9-1/source/dist/lib/iplike.so": Permission denied
at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139) at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152) at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100) at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43) at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50) at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:298) at org.opennms.install.Installer.updateIplike(Installer.java:1691) at org.opennms.install.Installer.install(Installer.java:265) at org.opennms.install.Installer.main(Installer.java:2450)
plz help me out
ALi
_________________________________________________________________ View Athlete s Collections with Live Search http://sportmaps.live.com/index.html?source=hmemailtaglinenov06&FORM=MGAC01
---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 3 answer | Add comment |
Monday, 2 November 2009
|
| Internal function call from C-language function Zoltan Boszormenyi 05:27:13 |
| | Hi,
I need to call date_part() from a C function. How to do that?
Thanks in advance, Zolt n B sz rm nyi
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
|
| | 9 answers | Add comment |
Friday, 4 September 2009
|
| suitable driver not found Rajitha M 18:45:44 |
| | The code given below gives an exception as: a suitable driver not found.
I am not working on a network, it is a simple stand alone PC. Which SqlDriverr would be suitable,I am using SqlServerDriverfor SQl database 7.0,j2sdk1.41_01 and have installed SqlServerDriver driver sqlserverdriver.jar
and put it in j2sdk1.41_01/jre/lib/ext/sqlserverdriver.jar and then set the classpath but while running this program i am getting the error like: classnotfound exception:java.lang.classnotfoundexception:com.microsoft.jdbc.sql server:SqlServerDriver sqlexception:java.sql.sqlexception:no suitabledriver and code is: import java.sql.*; import javax.sql.*; import java.io.*; import java.lang.*; public class connect { public static void main(String[] args) { String url = "jdbc:microsoft:sqlserver://idealab5.cs.uiowa.edu:1433;DatabaseName=Entertainment;"; // here Entertainment is database name String uid = "admin"; String pw = ""; try { // Load driver class Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); } catch (java.lang.ClassNotFoundException e) { System.err.println("ClassNotFoundException: " +e); } Connection con = null; try { con = DriverManager.getConnection(url,uid,pw); Statement stmt = con.createStatement(); ResultSet rst = stmt.executeQuery("SELECT * FROM USERS"); //System.out.println("Product Name, Price"); while (rst.next()) { System.out.println(rst.getString(1));//+","+rst.getDouble(2)); } } catch (SQLException ex) { System.err.println("SQLException: " + ex); } finally { if (con != null) try { con.close(); } catch (SQLException ex) { System.err.println("SQLException: " + ex); } } } }
--------------------------------- Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. |
| | 6 answers | Add comment |
Thursday, 16 April 2009
|
| How to get user names and passwords Zhaoyong Wu 00:05:01 |
| | Hi, I created a database and create some users and password. But how to get these list of users? What are these system table names? Can you tell me?
Thanks.
Zhaoyong Wu
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
| | 2 answer | Add comment |
Wednesday, 25 March 2009
|
| ODBC function mapping Peter Eisentraut 11:52:38 |
| | There seems to be a lot of overlap between the odbc.sql file and the conversion table in convert.c. Do we still need the odbc.sql file at all?
-- Peter Eisentraut peter_e@gmx.net
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
|
| | Add comment |
Friday, 13 February 2009
|
| solution for error 75 path/file access errpr Amol Bhosale 13:42:05 |
| | I have installed pgAdmin II v1.4.12 on my WinXP computer using the administrator account.
When I try to connect to a database-server using my own user-id, I get this run-time error '75': Path/File access error, and pgAdmin quits. When I log on as administrator I can connect to the server and everything works fine.
What can I do to run pgAdmin under my own account?
Sincerely AMOL
--------------------------------- Do you Yahoo!? Meet the all-new My Yahoo! – Try it today! |
| | 3 answer | Add comment |
Thursday, 18 December 2008
|
| Setting up DSPACE for Postgres access Ashwin Kutty 17:44:32 |
| | I am currently testing a product by the name of dspace (http://www.dspace.org) and require installing java since dspace is completely java based. The problem is that during its installation it needs to talk to pgsql via jdbc and I am getting the error: Exception occurred:org.apache.commons.dbcp.DbcpException: java.sql.SQLException: Driver not found for URL: jdbc ostgresql://dspace.library.dal.ca:5432/dspace org.apache.commons.dbcp.DbcpException: java.sql.SQLException: Driver not found for URL: jdbc ostgresql://dspace.library.dal.ca:5432/dspace
I cannot ofcourse go beyond this since they have to talk to each other and if that doesnt happen nothing else is going to work. In terms of the system and the env var's; here goes:
RH Linux 9 (kernel 2.4.20) j2sdk1.4.2_01 dspace - 1.1.1 postgres - 7.3.4
environment var's: CLASSPATH=/home/dspace/j2sdk1.4.2_01:/dspace/lib/postgresql.jar:/dspace/lib:/usr/local/pgsql/share/java:/dspace/config:/dspace/lib:/dspace/lib/activation.jar:/dspace/lib/commons-cli.jar:/dspace/lib/commons-collections.jar:/dspace/lib/commons-dbcp.jar:/dspace/lib/commons-pool.jar:/dspace/lib/cos.jar:/dspace/lib/dspace.jar:/dspace/lib/handle.jar:/dspace/lib/jena.jar:/dspace/lib/log4j.jar:/dspace/lib/lucene.jar:/dspace/lib/mail.jar:/dspace/lib/oaicat.jar:/dspace/lib/oro.jar:/dspace/lib/servlet.jar:/dspace/lib/xalan.jar:/dspace/lib/xerces.jar:/home/dspace/j2sdk1.4.2_01/lib:/home/dspace/j2sdk1.4.2_01:/home/dspace/j2sdk1.4.2_01/jre/lib:/home/dspace/j2sdk1.4.2_01/jre/javaws:/home/dspace/dspace-1.1.1-source/build/classes:/usr/local/pgsql/share/java:/usr/local/pgsql/share/java/postgresql.jar PGDATA=/usr/local/pgsql/data PGSQL_HOME=/usr/local/pgsql/bin JAVA_HOME=/home/dspace/j2sdk1.4.2_01 PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/dspace/apache-ant-1.5.4/bin:/usr/local/pgsql/share/java:/usr/local/pgsql/bin:/home/dspace/j2sdk1.4.2_01:/home/dspace/j2sdk1.4.2_01/bin:/home/dspace/bin JAVA_OPTS=-Xmx512M -Xms64M -Dfile.encoding=UTF-8
I do realise that support from the developers from DSPACE should be available but it has not been so far. As long as I get JDBC to work with PGSQL, it would at the very least get me started.
Any help would be greatly appreciated.
Thanks.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
|
| | 11 answers | Add comment |
Wednesday, 22 October 2008
|
| plpgsql - Insert from a record variable? Phil Endecott 14:20:15 |
| | Dear Postgresql experts,
I'm writing a simple plpgsql function that selects a row from a table, modifies it slightly, and inserts the modified version. This sounds simple, but I can't find how to do the insert in a simple generic fashion: although SELECT has a form that puts the result in a record variable, INSERT doesn't seem to have anything similar.
What I'd like to be able to write is something like this:
DECLARE R RECORD; BEGIN SELECT * INTO R FROM TABLE WHERE id=n; R.something := x; INSERT INTO TABLE R; END
But as far as I can see the only syntax that is allowed for INSERT is the normal INSERT INTO TABLE (x,y,z) VALUES (a,b,c). To achieve what I want to do I'd need to iterate over the fields of the record (how?) and build up the string of the INSERT query.
It seems odd that SELECT can use a record variable but INSERT can't, so I wonder if I have missed something. Any suggestions?
Thanks in advance for any help anyone can offer.
Regards,
--Phil.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
|
| | 8 answers | Add comment |
Wednesday, 10 September 2008
|
| calling a pl/pgsql function with array in argument Ruben Vivas 12:33:45 |
| | Hello, i am tryin to pass an array like argument to a pl/pgsql function but i can not find the correct data type in te function.
i am using php.
ex: $dato[][] is the array using php, and i need call:
1. $res = fn_vea ($dato); ?????
2. in pl/pgsql CREATE FUNCTION fn_vea (integer [][]) RETURNS varchar (25) AS .........
does it work?? thank you
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
| | Add comment |
Monday, 1 September 2008
|
| PgDatabase, on heap construction results in SIGSEGV. Savage 06:32:09 |
| | I am having a problem constructing a PgDatabase object. If I create the object on the stack (as a var local to a function) then it constructs and connects to the DB ok. But, if I create and construct the object on the heap, a SIGSEGV occurs within libpq++.so.4.
Has anyone got any ideas what could be causing this? I have only been programming under linux for a month or so at this stage, but I've a lot of Win32 experience. Any pointers to things that a Win32 to Linux newbie would not be aware of would be much appreciated.
Thanks, John
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
| | 1 answer | Add comment |
Tuesday, 19 August 2008
|
| Backup and restore through JDBC Marlon Petry 14:35:25 |
| | Hello List I am trying to develop, a API to carry through backup and restore through JDBC. I think that the best form is to use JNI. Some Suggestion?
|
| | 15 answers | Add comment |
|