Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have installed CentOS on a virtual machine and installed the latest version of PostgreSQL server 9.3. I have added PostGIS extension and some data.

For some reason, the PostgreSQL service now fails to start.

How can I get the table structure (with sequences, indexes, and relations) and data, and move it to another server?

share

migration rejected from unix.stackexchange.com 15 hours ago

This question came from our site for users of Linux, FreeBSD and other Un*x-like operating systems.. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

put on hold as off-topic by dezso, RolandoMySQLDBA, RLF, Paul White, Michael Green 15 hours ago

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, RolandoMySQLDBA, RLF
If this question can be reworded to fit the rules in the help center, please edit the question.

    
@Anatoly: why don't you try fixing the postgre server? What errors do you see in the logs? –  Mat Dec 24 '13 at 11:59
    
@Mat /var/log/pgsql is empty. grep -r "postgresql\-9\.3" /var/log output is boot log [fail] and binary wtmp match. I don't know where to search. –  Anatoliy Gusarov Dec 24 '13 at 12:46
    
Assuming a default install of postgres is the same on Centos as on RedHat, you should look in /var/lib/pgsql/data/pg_log. –  Mat Dec 24 '13 at 12:55
    
@Mat found log in /var/lib/pgsql/9.3/pgstartup.log found that I need to remove postmaster.pid and start server. Server started, but now I can't connect to my db, now get error psql: FATAL: could not read block 3 in file "base/17832/12660": read only 0 of 8192 bytes =( –  Anatoliy Gusarov Dec 24 '13 at 13:03
1  
Nonono, do not just remove postmaster.pid unless you've made absolutely certain that all PostgreSQL back-end processes are terminated, i.e. there's no postgres in a ps listing. Given the error, treat your DB as severely corrupt. Look at wiki.postgresql.org/wiki/Corruption, stackoverflow.com/a/15201977/398670, and this somewhat related post stackoverflow.com/a/12472582/398670 –  Craig Ringer Dec 25 '13 at 9:03

1 Answer 1

Probably some system index or system table is broken. PostgreSQL reads these indexes on start. You can try to fix by REINDEX on system columns in single user mode. ATTENTION: this mode is very dangerous - and necessary to backup data directory first

(standalone console is leaved by pressing ctrl-d)

[postgres@nemesis pavel]$ /usr/local/pgsql/bin/postgres --single -D /usr/local/pgsql/data/ template0

PostgreSQL stand-alone backend 9.4devel
backend> REINDEX DATABASE template0;
backend> 

Next, you can try start server

share
    
Thanks for the answer, but I reinstalled system and restored data from dump. BTW had problems with restoring data because I have two schemes - public and topology (postGIS ext.). I use Navicat Premium for windows, and have used dump function in it. As I am new in PostgreSQL I trusted to navicat and made two dumps - one for each topologies. Well, thing is that I have relations between tables in topologies, and separate restore of dumps was unsuccessful. But now I know how to create sequences =) –  Anatoliy Gusarov Dec 30 '13 at 17:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.