Postgresql – Restoring schemas with the sequences

backuppostgresqlschemasequence

This evening a developer asked to restore only schemas from a database into another database.
A colleague of mine did this, but after that the developer came to me saying that the sequences are missing from the restored schema.

I think this was the command my colleague used to get a dump of the schema only :

/usr/local/pgsql915/bin/pg_dump -U backupuser --schema-only myegrtx -p 5432 -t tb_roadtax_kioskinfo -t tb_rtx_expdelivery_postcode -t tb_roadtax_inquiryinfo -t roadtax_tx_history -t tb_roadtax_payment_data_info -t tb_roadtax_renewalinfo -t tb_roadtax_kioskusers -t tb_roadtax_kioskuser_trans -t tb_roadtax_delivaryinfo -t tb_roadtax_asinfo  -t tb_roadtax_disk_print_info > myegrtxbike-schema.sql

And this was the command to restore it to the new db :

/usr/local/pgsql934/bin/psql -U postgres -d myegrtxbike -p 6434 -f srikanth/myegrtxbike-schema.sql

How do I now get the backup of and restore the sequences that have been reported missing?

I need some guide on this, as googling the commands is not a success (The commands do not work or I do not get the proper commands, especially for the sequence part).

Best Answer

Simply add -t <sequencename> for all the sequences you want to dump.

Related Solutions

PostgreSQL 9.1 Hot Backup Error: the database system is starting up

The message "The database system is starting up." does not indicate an error. The reason it is at the FATAL level is so that it will always make it to the log, regardless of the setting of log_min_messages:

http://www.postgresql.org/docs/9.1/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN

After the rsync, did you really run what you show?:

pgsql -c "select pg_stop_backup();";

Since there is, so far as I know, no pgsql executable, that would leave the backup uncompleted, and the slave would never come out of recovery mode. On the other hand, maybe you really did run psql, because otherwise I don't see how the slave would have logged such success messages as:

Log: consistent recovery state reached at 0/BF0000B0

and:

Log: streaming replication successfully connected to primary

Did you try connecting to the slave at this point? What happened?

The "Success. You can now start..." message you mention is generated by initdb, which shouldn't be run as part of setting up a slave; so I think you may be confused about something there. I'm also concerned about these apparently conflicting statements:

The only ways I have restarted Postgres is through the service postgresql-9.1 restart or /etc/init.d/postgresql-9.1 restart commands. After I receive this error, I kill all processes and again try to restart the database...

Did you try to stop the service through the service script? What happened? It might help in understanding the logs if you prefixed lines with more information. We use:

log_line_prefix = '[%m] %p %q<%u %d %r> '

The recovery.conf script looks odd. Are you copying from the master's pg_xlog directory, the slave's active pg_xlog directory, or an archive directory?

Oracle sqldeveloper Database Diff – not seeing indexes/sequences

This may not be an answer but worth to check up. Some indexes may have been set to invisible as an alternative to being dropped. These are still maintained by the database but ignored by the optimizer. You'll be able to see them by querying the dictionary, but the compare tool may ignore them.

SELECT INDEX_NAME, INDEX_TYPE, VISIBILITY
FROM ALL_INDEXES
WHERE INDEX_NAME LIKE 'EMP%';

Related Question