How to upload postgresql database content to django project on digital ocean

Question

As I was following through the tutorial on this subject https://www.digitalocean.com/community/tutorials/how-to-install-the-django-web-framework-on-ubuntu-18-04
I successfully deployed my django application with postgresql db on digital ocean. However I constantly fail to upload content of the db from my local computer to digital ocean. I always get an empty db after upload. Is there a way to fix it?

Answer 1

marks567217 September 25, 2020

Try this article
https://www.digitalocean.com/community/tutorials/how-to-use-postgresql-with-your-django-application-on-ubuntu-16-04

How To Use PostgreSQL with your Django Application on Ubuntu 16.04

by Justin Ellingwood

Django is a flexible framework for quickly creating Python applications. By default, Django applications are configured to store data into a lightweight SQLite database file. While this works well under some loads, a more traditional DBMS can improve performance in...

Reply Report

BiZonNN September 27, 2020

Thanks for the comment. I followed the procedure earlier but it does not fix my problem.
Reply Report

Answer 2

BiZonNN September 27, 2020

Thanks for the comment. I followed the procedure earlier but it does not fix my problem.
Reply Report

Answer 3

Yannek September 25, 2020

Hi,

If you need to do initial data upload to your database, consider the method with a dump file. It is quite reliable one. So, make a dump file of a database on your local computer, then send the dump file to your droplet, and restore your database from it on your droplet.

You can obtain detailed info on each step from the following resources:

Creating a database dump file, and restoring a database from a file:
https://www.postgresql.org/docs/13/backup-dump.html

Transferring files using SFTP
https://www.digitalocean.com/community/tutorials/how-to-use-sftp-to-securely-transfer-files-with-a-remote-server

How To Use SFTP to Securely Transfer Files with a Remote Server

by Justin Ellingwood

SFTP is a secure way to transfer files between local and remote servers. Much more secure than FTP, this protocol uses an SSH tunnel to establish a connection and uses encryption to transfer files in an interactive session.

Reply Report

BiZonNN September 25, 2020

That’s exactly what I tried to do. I have no problem with creating a db dump on my local computer & transferring the dump file to the server. What I need to know is where to restore the file. I can’t find where my database is stored on the server.
I am exploring the etc directory but still can’t find the database itself.
Reply Report
- Yannek September 26, 2020
  
  OK, so you have your dump file transferred to your droplet. Now, you need to create your database:
  
  createdb -U postgres -h localhost -T template0 db_name
  
  Replace db_name with your actual database name.
  
  Then restore data from the dump file into newly-created database.
  
  pg_restore -U postgres -h localhost -d db_name dump_file
  
  Replace db_name with your actual database name, and dump_file with your actual dump file. After restoring data, you can list the tables to ensure that everything went fine:
  
  psql -U postgres -h localhost -W -c "select table_schema, table_name from information_schema.tables where table_schema not in ('information_schema', 'pg_catalog') and table_type = 'BASE TABLE' order by table_schema, table_name;" -d db_name
  
  Let me know how it works.
  
  Reply Report
  
  BiZonNN September 27, 2020
  
  I created the db earlier when I was deploying the project. Anyway, I tried to restore the back_up file in postgres command line. It just switched me to the next line (postgres-#) & at the same time completely screwed my project. Now the browser shows “502 Bad Gateway nginx/1.14.0 (Ubuntu)”.
  I can’t believe there is now easy way to upload db content to the server
  
  Reply Report
  
  Yannek September 27, 2020
  
  @BiZonNN wroteNow the browser shows “502 Bad Gateway nginx/1.14.0 (Ubuntu)”.
  
  I am guessing now because I do not know what your whole environment is. To me, it looks like your nginx server acts as reverse proxy and got an invalid response from back-end server (embedded Django web server ?). It looks like your Django app serves dynamic content pulling it from psql database.
  So, please, compare the contents of your local database and droplet’s database, and find what differs. To do that you can use SQL queries as well as a tool like pgAdmin.
  
  How did you create a dump file of your local database ? Can you provide exact command you ran to create it ?
  
  Reply Report
  
  BiZonNN September 27, 2020
  
  It turned out that my problem with ‘502 Bad Gateway nginx/1.14.0 (Ubuntu) had nothing to do with postgres changes.
  Somehow I screwed 'settings.py’ file. Deleted 'django.contrib.auth’ from INSTALLED_APPS.
  
  As for transferring postgres db contents to DO, I am still working.
  
  I have created my dbbackup file in pgAdmin4. Moved it to the server. And use console to restore it to the existing db.
  
  Ubuntu does not let me do it. The problem may be in user permissions. Root user is switched off on my droplet. I am working under another administrative account. I’m gonna try to restore it as a root user.
  
  Reply Report

Answer 4

BiZonNN September 25, 2020

That’s exactly what I tried to do. I have no problem with creating a db dump on my local computer & transferring the dump file to the server. What I need to know is where to restore the file. I can’t find where my database is stored on the server.
I am exploring the etc directory but still can’t find the database itself.
Reply Report
- Yannek September 26, 2020
  
  OK, so you have your dump file transferred to your droplet. Now, you need to create your database:
  
  createdb -U postgres -h localhost -T template0 db_name
  
  Replace db_name with your actual database name.
  
  Then restore data from the dump file into newly-created database.
  
  pg_restore -U postgres -h localhost -d db_name dump_file
  
  Replace db_name with your actual database name, and dump_file with your actual dump file. After restoring data, you can list the tables to ensure that everything went fine:
  
  psql -U postgres -h localhost -W -c "select table_schema, table_name from information_schema.tables where table_schema not in ('information_schema', 'pg_catalog') and table_type = 'BASE TABLE' order by table_schema, table_name;" -d db_name
  
  Let me know how it works.
  
  Reply Report
  
  BiZonNN September 27, 2020
  
  I created the db earlier when I was deploying the project. Anyway, I tried to restore the back_up file in postgres command line. It just switched me to the next line (postgres-#) & at the same time completely screwed my project. Now the browser shows “502 Bad Gateway nginx/1.14.0 (Ubuntu)”.
  I can’t believe there is now easy way to upload db content to the server
  
  Reply Report
  
  Yannek September 27, 2020
  
  @BiZonNN wroteNow the browser shows “502 Bad Gateway nginx/1.14.0 (Ubuntu)”.
  
  I am guessing now because I do not know what your whole environment is. To me, it looks like your nginx server acts as reverse proxy and got an invalid response from back-end server (embedded Django web server ?). It looks like your Django app serves dynamic content pulling it from psql database.
  So, please, compare the contents of your local database and droplet’s database, and find what differs. To do that you can use SQL queries as well as a tool like pgAdmin.
  
  How did you create a dump file of your local database ? Can you provide exact command you ran to create it ?
  
  Reply Report
  
  BiZonNN September 27, 2020
  
  It turned out that my problem with ‘502 Bad Gateway nginx/1.14.0 (Ubuntu) had nothing to do with postgres changes.
  Somehow I screwed 'settings.py’ file. Deleted 'django.contrib.auth’ from INSTALLED_APPS.
  
  As for transferring postgres db contents to DO, I am still working.
  
  I have created my dbbackup file in pgAdmin4. Moved it to the server. And use console to restore it to the existing db.
  
  Ubuntu does not let me do it. The problem may be in user permissions. Root user is switched off on my droplet. I am working under another administrative account. I’m gonna try to restore it as a root user.
  
  Reply Report

Answer 5

BiZonNN September 29, 2020

I am trying to restore the file on DO in DO console using different commands, such as:
psql dbname < dumpfile and others.
It doesn’ work.

Reply Report

Yannek September 29, 2020
So, is your droplet’s database still empty ? Can you see any tables in the droplet’s database ? To check it, in the following command replace user postgres and database name db_name with your actual names.

psql --user postgres --password --host=localhost --db db_name --command '\dt'

What does postgresql’s log say ? List the content of PSQL log directory:

sudo ls -la /var/log/postgresql/

and replace log_name with the newest listed log file, if it is not empty (size 0), of course :)

sudo tail -50 /var/log/postgresql/log_name
Reply Report
- BiZonNN September 29, 2020
  
  My droplet has not been empty since the very beginning. In the process of uploading my django project to DO server I created a postgresql db & successfully copied all my tables from the local computer to this db.
  When I connect to the server db & type \dt, it shows all my tables which makes me think that all them exist.
  The problem is that I can’t copy data which is contained in the tables of the db on my local computer, to the server db.
  
  Reply Report
  
  Yannek September 29, 2020
  
  If the structures of your droplet’s database and local database are the same, you can use dump file for the initial data upload.
  It might happen that your created your dump file containing just the database structure (schema), without any data. And every time, when you were trying to restore database from that file on your droplet, you got just the structure. Take a look into your dump file and check if it contains any data, e.g.
  
  cat dump_file_name | more
  
  Reply Report

Answer 6

Yannek September 29, 2020
So, is your droplet’s database still empty ? Can you see any tables in the droplet’s database ? To check it, in the following command replace user postgres and database name db_name with your actual names.

psql --user postgres --password --host=localhost --db db_name --command '\dt'

What does postgresql’s log say ? List the content of PSQL log directory:

sudo ls -la /var/log/postgresql/

and replace log_name with the newest listed log file, if it is not empty (size 0), of course :)

sudo tail -50 /var/log/postgresql/log_name
Reply Report
- BiZonNN September 29, 2020
  
  My droplet has not been empty since the very beginning. In the process of uploading my django project to DO server I created a postgresql db & successfully copied all my tables from the local computer to this db.
  When I connect to the server db & type \dt, it shows all my tables which makes me think that all them exist.
  The problem is that I can’t copy data which is contained in the tables of the db on my local computer, to the server db.
  
  Reply Report
  
  Yannek September 29, 2020
  
  If the structures of your droplet’s database and local database are the same, you can use dump file for the initial data upload.
  It might happen that your created your dump file containing just the database structure (schema), without any data. And every time, when you were trying to restore database from that file on your droplet, you got just the structure. Take a look into your dump file and check if it contains any data, e.g.
  
  cat dump_file_name | more
  
  Reply Report

Answer 7

BiZonNN September 29, 2020

I opened the las log file & that’s what is says:
“Cannot allocate memory”
“Could not fork autovacuum worker process”
“Worker took too long to start; cancelled
"Autovacuum started without a worker entry”
To me it looks chinese

Reply Report

Yannek September 29, 2020
You need to sort it out first. Run this command:

free -m

and this one:

cat /proc/meminfo

and provide the results, please.
Reply Report

Answer 8

Yannek September 29, 2020
You need to sort it out first. Run this command:

free -m

and this one:

cat /proc/meminfo

and provide the results, please.
Reply Report

Related

Question

How to upload postgresql database content to django project on digital ocean

Related

Leave a comment

Looking for something else?

Sign up for our newsletter

Related

Question

How to upload postgresql database content to django project on digital ocean

Related

Leave a comment

Related

question

How to connect managed database (postgres) with ssl-mode="varify-full" in django app?

question

Is possible to connect a DO K8s cluster with a DO Droplet private network?

question

How did my database got hacked ?

question

IP, domain name and nginx

Looking for something else?