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|improve this question

migrated from unix.stackexchange.com Dec 25 '13 at 5:39

This question came from our site for users of Linux, FreeBSD and other Un*x-like operating systems..

    
@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
    
Looks like stuff's corrupted (unclean shutdown?). Not a pg specialist, all I can say is: time to recover from backups... –  Mat Dec 24 '13 at 13:08
show 3 more comments

1 Answer

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|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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