I create a database, some tables and stored procedures on my local postgres server. Now I want to automatically migrate these things to my production server without creating everything from scratch again. This should be something quite straight-forward, however I did not find any books trying to explain this. So I wonder how could it be achieved? Can someone please provide some link of tutorials? Thanks!
Use pg_dump for this purpose, Postgres has great tutorial. For single schema instruction may be the following:
pg_dump -o -h hostname -U db_username -d db_name -n schema_name > your_schema.dump
Sample restore command:
psql -h hostname -U db_username db_name < your_schema.dump